首页新闻找找看学习计划

复杂的SQL语句,求解

0
悬赏园豆:20 [已解决问题] 解决于 2018-01-19 15:40

select CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' channel_name UNION ALL SELECT '-1' channel_name) t
{{WHERE channel}}) = 1
THEN
'合计'
ELSE t.channel_name
END channel_name,
CAST(CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' prod_price UNION ALL SELECT '-1' prod_price) t
{{WHERE product}}) = 1
THEN
'合计'
ELSE t.prod_price
END AS CHAR) prod_price,
CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' province_name UNION ALL SELECT '-1' province_name) t
{{WHERE province}}) = 1
THEN
'合计'
ELSE t.province_name
END province_name,
CAST(CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' star_level UNION ALL SELECT '-1' star_level) t
{{WHERE star_level}}) = 1
THEN
'合计'
ELSE t.star_level
END AS CHAR) star_level,
sum(t.cover_user_cnt) cover_user_cnt,
sum(t.arrive_user_cnt) arrive_user_cnt,
sum(t.activation_user_cnt) activation_user_cnt,
sum(t.renew_user_cnt) renew_user_cnt
from (

SELECT
'短信' channel_name,
sxsp.SMS_PRODUCT_PRICE prod_price,
sxtd.PRO_NAME province_name,
sxrsq.RIGHTS_STAR star_level,
COUNT(*) cover_user_cnt,
SUM(CASE
WHEN sxsp.SMS_SEND_RESULT IN (0 , 3) THEN 1
ELSE 0
END) arrive_user_cnt,
SUM(CASE
WHEN sxoi.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) activation_user_cnt,
SUM(CASE
WHEN sxoi1.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) renew_user_cnt
FROM
SOURCE_XINYUAN_SMS_PRESEND sxsp
LEFT JOIN
SOURCE_XINYUAN_RIGHTS_STAR_QUERY sxrsq ON sxsp.SMS_QUERY_ID = sxrsq.RIGHTS_ID
AND sxrsq.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_TERM_DATA sxtd ON sxtd.TERM_ID = sxrsq.RIGHTS_TERM_ID and sxtd.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi ON sxoi.ORD_ID = sxsp.SMS_ORDER_ID
AND sxoi.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi1 ON sxoi1.PHONE_NUMBER = sxoi.PHONE_NUMBER
AND sxoi1.BUSS_TYPE = 2
AND sxoi1.IS_DELETED = 0
WHERE
sxsp.SMS_SEND_TIME >= {{lval order_date}}
AND sxsp.SMS_SEND_TIME <= {{rval order_date}}
AND
sxsp.IS_DELETED = 0
AND sxsp.SMS_TYPE = 1
GROUP BY sxsp.SMS_PRODUCT_PRICE,
sxtd.PRO_NAME,
sxrsq.RIGHTS_STAR


union all

SELECT
'外呼',
sxocat.TARGET_PRODUCT_PRICE prod_price,
mapp.SOURCE_PROVINCE_NAME province_name,
sxocat.TARGET_STARS star_level,
COUNT(*),
SUM(CASE
WHEN sxoil.LOG_CALLOUT_RESULT IN (1 , 2) THEN 1
ELSE 0
END),
SUM(CASE
WHEN sxoi.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) activation_user_cnt,
SUM(CASE
WHEN sxoi1.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) renew_user_cnt
FROM
SOURCE_XINYUAN_OUTCALL_IMPORT_LOG sxoil
LEFT JOIN
SOURCE_XINYUAN_OUTCALL_ACTIVITY_TARGET sxocat ON sxocat.TARGET_ID = sxoil.LOG_TARGET_ID
AND sxocat.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_OUTCALL_ACTIVITY sxoa ON sxoa.ACTIVITY_ID = sxocat.ACTIVITY_ID
AND sxoa.IS_DELETED = 0
LEFT JOIN
MAPPING_PROVINCE mapp ON mapp.ID = sxocat.MAPPING_TARGET_PROVINCE_ID
left join SOURCE_XINYUAN_OUTCALL_RECORD sxor on sxor.TARGET_ID=sxoil.LOG_TARGET_ID
and sxor.IS_DELETED=0
left join SOURCE_XINYUAN_APPLY_INFO sxai on sxai.APPLY_ID=sxor.APPLY_ID and sxai.IS_DELETED=0
left join SOURCE_XINYUAN_ORDER_INFO sxoi on sxoi.ORD_ID=sxai.ORD_ID and sxoi.IS_DELETED=0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi1 ON sxoi1.PHONE_NUMBER = sxoi.PHONE_NUMBER
AND sxoi1.BUSS_TYPE = 2
AND sxoi1.IS_DELETED = 0
WHERE
sxoil.LOG_OUTCALL_TIME >= {{lval order_date}}
AND sxoil.LOG_OUTCALL_TIME <= {{rval order_date}}
AND
sxoa.ACTIVITY_SOURCE = 101
AND sxoil.IS_DELETED = 0
AND sxoil.LOG_OUTCALL_TIME = (SELECT
MAX(sxoil1.LOG_OUTCALL_TIME)
FROM
SOURCE_XINYUAN_OUTCALL_IMPORT_LOG sxoil1
WHERE
sxoil1.LOG_TARGET_ID = sxoil.LOG_TARGET_ID
AND sxoil1.IS_DELETED = 0)
GROUP BY sxocat.TARGET_PRODUCT_PRICE,
mapp.SOURCE_PROVINCE_NAME,
sxocat.TARGET_STARS

union all

SELECT
sxai.CHANNEL_NAME channel_name,
mapp.PROD_PRICE prod_price,
sxaui.PROVINCE_DESC province_name,
sxaui.STAR_LEVEL star_level,
COUNT(*) cover_user_cnt,
COUNT(*) arrive_user_cnt,
SUM(CASE
WHEN sxoi.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) activation_user_cnt,
SUM(CASE
WHEN sxoi1.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) renew_user_cnt
FROM
SOURCE_XINYUAN_APPLY_INFO sxai
LEFT JOIN
MAPPING_PRODUCT mapp ON mapp.PROD_ID = sxai.PROD_ID
LEFT JOIN
SOURCE_XINYUAN_APPLY_USER_INFO sxaui ON sxaui.APPLY_ID = sxai.APPLY_ID
AND sxaui.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi ON sxai.APPLY_ID = sxoi.APPLY_ID
AND sxoi.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi1 ON sxoi1.PHONE_NUMBER = sxoi.PHONE_NUMBER
AND sxoi1.BUSS_TYPE = 2
AND sxoi1.IS_DELETED = 0
WHERE
sxai.XY_CREATE_TIME >= {{lval order_date}}
AND sxai.XY_CREATE_TIME <= {{rval order_date}}
AND
sxai.BUSS_TYPE IN (1 , 4)
AND sxai.CHANNEL_ID = 101001
AND sxai.IS_DELETED = 0
GROUP BY sxai.CHANNEL_NAME,
mapp.PROD_PRICE,
sxaui.PROVINCE_DESC,
sxaui.STAR_LEVEL

union all


SELECT
sxai.CHANNEL_NAME channel_name,
mapp.PROD_PRICE prod_price,
sxaui.PROVINCE_DESC province_name,
sxaui.STAR_LEVEL star_level,
COUNT(*) cover_user_cnt,
COUNT(*) arrive_user_cnt,
SUM(CASE
WHEN sxoi.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) activation_user_cnt,
SUM(CASE
WHEN sxoi1.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) renew_user_cnt
FROM
SOURCE_XINYUAN_APPLY_INFO sxai
LEFT JOIN
MAPPING_PRODUCT mapp ON mapp.PROD_ID = sxai.PROD_ID
LEFT JOIN
SOURCE_XINYUAN_APPLY_USER_INFO sxaui ON sxaui.APPLY_ID = sxai.APPLY_ID
AND sxaui.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi ON sxai.APPLY_ID = sxoi.APPLY_ID
AND sxoi.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi1 ON sxoi1.PHONE_NUMBER = sxoi.PHONE_NUMBER
AND sxoi1.BUSS_TYPE = 2
AND sxoi1.IS_DELETED = 0
WHERE
sxai.XY_CREATE_TIME >= {{lval order_date}}
AND sxai.XY_CREATE_TIME <= {{rval order_date}}
AND
sxai.BUSS_TYPE IN (1 , 4)
AND sxai.CHANNEL_ID = 101002
AND sxai.IS_DELETED = 0
GROUP BY sxai.CHANNEL_NAME,
mapp.PROD_PRICE,
sxaui.PROVINCE_DESC,
sxaui.STAR_LEVEL

union all


SELECT
'其他' channel_name,
mapp.PROD_PRICE prod_price,
sxaui.PROVINCE_DESC province_name,
sxaui.STAR_LEVEL star_level,
COUNT(*) cover_user_cnt,
COUNT(*) arrive_user_cnt,
SUM(CASE
WHEN sxoi.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) activation_user_cnt,
SUM(CASE
WHEN sxoi1.STATUS_CODE IN (200 , 998, 999) THEN 1
ELSE 0
END) renew_user_cnt
FROM
SOURCE_XINYUAN_APPLY_INFO sxai
LEFT JOIN
MAPPING_PRODUCT mapp ON mapp.PROD_ID = sxai.PROD_ID
LEFT JOIN
SOURCE_XINYUAN_APPLY_USER_INFO sxaui ON sxaui.APPLY_ID = sxai.APPLY_ID
AND sxaui.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi ON sxai.APPLY_ID = sxoi.APPLY_ID
AND sxoi.IS_DELETED = 0
LEFT JOIN
SOURCE_XINYUAN_ORDER_INFO sxoi1 ON sxoi1.PHONE_NUMBER = sxoi.PHONE_NUMBER
AND sxoi1.BUSS_TYPE = 2
AND sxoi1.IS_DELETED = 0
WHERE
sxai.XY_CREATE_TIME >= {{lval order_date}}
AND sxai.XY_CREATE_TIME <= {{rval order_date}}
AND
sxai.BUSS_TYPE IN (1 , 4)
AND sxai.CHANNEL_ID not in (101001,103001,102001,101002)
AND sxai.IS_DELETED = 0
GROUP BY mapp.PROD_PRICE,
sxaui.PROVINCE_DESC,
sxaui.STAR_LEVEL
) t
where 1=1
{{channel}}
{{product}}
{{province}}
{{star_level}}
group by
CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' channel_name UNION ALL SELECT '-1' channel_name) t
{{WHERE channel}}) = 1
THEN
'合计'
ELSE t.channel_name
END,
CAST(CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' prod_price UNION ALL SELECT '-1' prod_price) t
{{WHERE product}}) = 1
THEN
'合计'
ELSE t.prod_price
END AS CHAR),
CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' province_name UNION ALL SELECT '-1' province_name) t
{{WHERE province}}) = 1
THEN
'合计'
ELSE t.province_name
END,
CASE
WHEN
(SELECT
COUNT(1)
FROM
(SELECT '0' star_level UNION ALL SELECT '-1' star_level) t
{{WHERE star_level}}) = 1
THEN
'合计'
ELSE t.star_level
END

优秀的男人的主页 优秀的男人 | 初学一级 | 园豆:184
提问于:2018-01-19 09:12
< >
分享
最佳答案
1

果然很复杂,为什么不用视图来简化一下。mssql的话用临时表,mysql没写个存储过程。

收获园豆:15
数据酷软件 | 初学一级 |园豆:41 | 2018-01-19 09:51

因为夹杂了很多业务,所以必须写复杂的SQL语句来满足需要

优秀的男人 | 园豆:184 (初学一级) | 2018-01-19 09:55
其他回答(2)
1

你得说你要解决什么问题啊。

收获园豆:5
87Super | 园豆:121 (初学一级) | 2018-01-19 09:33

我对这个语句很不理解,能不能给我解释下这个语句什么意思

支持(0) 反对(0) 优秀的男人 | 园豆:184 (初学一级) | 2018-01-19 09:36

很明显是想简化,变得可维护。这么长心里有点不安呗。呵呵

支持(0) 反对(0) 数据酷软件 | 园豆:41 (初学一级) | 2018-01-19 09:53

@优秀的男人:仔细看看出来的结果集,然后推断这个语句的作用。外人不太可能一下看出来的。

支持(0) 反对(0) 数据酷软件 | 园豆:41 (初学一级) | 2018-01-19 09:56

@数据酷软件: 那语句里面有好多函数,像cast,case when,else,end,那都是什么

支持(0) 反对(0) 优秀的男人 | 园豆:184 (初学一级) | 2018-01-19 09:59

@优秀的男人: cast(now() as date)比如这个是一个转换函数把日期时间转换成日期 case when end 是这样的比如: case 0 then '男', case 1 then '女', else '未知' end as '性别'

支持(0) 反对(0) 数据酷软件 | 园豆:41 (初学一级) | 2018-01-19 10:08

@数据酷软件: end as '性别'是别名么

支持(0) 反对(0) 优秀的男人 | 园豆:184 (初学一级) | 2018-01-19 10:10

@优秀的男人:列名

我只有mssql没有mysql

支持(0) 反对(0) 数据酷软件 | 园豆:41 (初学一级) | 2018-01-19 10:17

@优秀的男人: 

看了一下这个SQL不是太复杂。

你掌握几个东西 应该就可以看懂了

看的时候一行一行写注释的读

里面涉及的东西

 LEFT JOIN表连接

UNION ALL 合并

CASE WHEN 百度一下 把以上这些知识掌握

支持(0) 反对(0) 87Super | 园豆:121 (初学一级) | 2018-01-19 10:51

@优秀的男人: 

按复杂程度来说,不算复杂。只是连接的表多一点而已。

支持(0) 反对(0) 87Super | 园豆:121 (初学一级) | 2018-01-19 10:52
1

挺复杂的啊

~扎克伯格 | 园豆:1799 (小虾三级) | 2018-01-19 10:04
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册