[技术交流] 有mysql高手么。问个查询语句【新的高级问题】[搞定了！] [复制链接]
查看:747 | 回复:3

超级大马甲 · 发表于 2019-8-25 15:10:52

本帖最后由超级大马甲于 2019-8-25 19:47 编辑

下面的语句只能输出

click_date	count	money
2019-08-25	10	12.11
2019-08-24	9	10
2019-08-23	5	100
时间	记录数量	价格 (第一条) 【下面Sql只能输出这样】
时间	记录数量	所有记录的总价格【我想要这样的】 Sql已改成想要的了

select a.click_date,ifnull(b.count,0) as count,money
from (
SELECT curdate() as click_date
union all
SELECT date_sub(curdate(), interval 14 day) as click_date
union all
SELECT date_sub(curdate(), interval 13 day) as click_date
union all
SELECT date_sub(curdate(), interval 12 day) as click_date
union all
SELECT date_sub(curdate(), interval 11 day) as click_date
union all
SELECT date_sub(curdate(), interval 10 day) as click_date
union all
SELECT date_sub(curdate(), interval 9 day) as click_date
union all
SELECT date_sub(curdate(), interval 8 day) as click_date
union all
SELECT date_sub(curdate(), interval 7 day) as click_date
union all
SELECT date_sub(curdate(), interval 6 day) as click_date
union all
SELECT date_sub(curdate(), interval 5 day) as click_date
union all
SELECT date_sub(curdate(), interval 4 day) as click_date
union all
SELECT date_sub(curdate(), interval 3 day) as click_date
union all
SELECT date_sub(curdate(), interval 2 day) as click_date
union all
SELECT date_sub(curdate(), interval 1 day) as click_date
) a left join (
select FROM_UNIXTIME(asttime, '%Y-%m-%d') AS datetime,sum(money) as money, count(*) as count
from cp_money WHERE status=1 and type=1 and asttime > UNIX_TIMESTAMP(date_sub(curdate(), interval 14 day))
group by datetime
) b on a.click_date = b.datetime ORDER BY click_date DESC

复制代码

******************************************已解决分割线**************************************************************
例如：

表 cp_message 我要查询 goto='s' 同时 aid=1 还同时要包含aid=null

有啥方法么？

SELECT * FROM `cp_message` WHERE goto = 's' and sid='1'

这样只能查询到 sid=1的 sid为空的没列出。

有点奇葩，有没有其它的方案么？

因为sid为空是全部人都要求收到，sid为当前的id 怎么才能混合进去呢？

ytomorrow · 发表于 2019-8-25 15:18:38

SELECT * FROM `cp_message` WHERE goto = 's' and (sid='1' or sid is null)

超级大马甲 · 发表于 2019-8-25 17:38:10

ytomorrow 发表于 2019-8-25 15:18
SELECT * FROM `cp_message` WHERE goto = 's' and (sid='1' or sid is null)

大神再顺便请教个sql，能搞不？
下面的语句只能输出

click_date	count	money
2019-08-25	10	12.11
2019-08-24	9	10
2019-08-23	5	100
时间	记录数量	价格 (第一条) 【下面Sql只能输出这样】
时间	记录数量	所有记录的总价格【我想要这样的】

select a.click_date,ifnull(b.count,0) as count,ifnull(b.money,0) as money
from (
SELECT curdate() as click_date
union all
SELECT date_sub(curdate(), interval 14 day) as click_date
union all
SELECT date_sub(curdate(), interval 13 day) as click_date
union all
SELECT date_sub(curdate(), interval 12 day) as click_date
union all
SELECT date_sub(curdate(), interval 11 day) as click_date
union all
SELECT date_sub(curdate(), interval 10 day) as click_date
union all
SELECT date_sub(curdate(), interval 9 day) as click_date
union all
SELECT date_sub(curdate(), interval 8 day) as click_date
union all
SELECT date_sub(curdate(), interval 7 day) as click_date
union all
SELECT date_sub(curdate(), interval 6 day) as click_date
union all
SELECT date_sub(curdate(), interval 5 day) as click_date
union all
SELECT date_sub(curdate(), interval 4 day) as click_date
union all
SELECT date_sub(curdate(), interval 3 day) as click_date
union all
SELECT date_sub(curdate(), interval 2 day) as click_date
union all
SELECT date_sub(curdate(), interval 1 day) as click_date
) a left join (
select FROM_UNIXTIME(asttime, '%Y-%m-%d') AS datetime,money, count(*) as count
from cp_money WHERE status=1 and type=1 and asttime > UNIX_TIMESTAMP(date_sub(curdate(), interval 14 day))
group by datetime
) b on a.click_date = b.datetime ORDER BY click_date DESC

复制代码

ytomorrow · 发表于 2019-8-25 21:50:41

本帖最后由 ytomorrow 于 2019-8-25 22:09 编辑

select FROM_UNIXTIME(asttime, '%Y-%m-%d') AS datetime,sum(money) as total, count(*) as count from cp_money WHERE status=1 and type=1 and asttime > UNIX_TIMESTAMP(date_sub(curdate(), interval 14 day))
group by datetime

前面和后面不变，你组合一下

如果不需要空数据的话，可以只用这一条语句

百独托管7500 紫田网络	超高转化播放器收cps[推荐]	速盾CDN 免实名免备防屏蔽	阿里云爆款特卖9.9元封顶	提升alexa、IP流量7Q5团队
【腾讯云】中小企福利专场	【腾讯云】多款产品1折起	高防随时退换好耶数据	小飞国外网赚带你月入万元	炎黄网络4H4G10M 99每月
香港带宽CN2/美国站群优惠	中客数据中心服务器租用	联盟系统移动广告平台中易	企业专场腾讯云服务器2.5折	九九数据工信部正规资质
腾讯云新用户大礼包代金券	高价收cpa注册量高价展示	【腾讯云】2核2G/9.93起	租服务器找45互联随时退换	阿里云短信服务验证秒达

[技术交流] 有mysql高手么。问个查询语句【新的高级问题】[搞定了！] [复制链接]
查看:747 | 回复:3

点评

评分

点评

浏览过的版块

落伍手机绑定

落伍微信绑定

落伍者

[技术交流] 有mysql高手么。问个查询语句 【新的高级问题】[搞定了！] [复制链接] 查看:747 | 回复:3

点评

评分

点评

浏览过的版块

落伍手机绑定

落伍微信绑定

落伍者

[技术交流] 有mysql高手么。问个查询语句【新的高级问题】[搞定了！] [复制链接]
查看:747 | 回复:3