-
-
张瑞阿 初学数据Lv2
发表于2023-1-3 09:53
悬赏1
未解决
楼主
各位大佬，求救、设置浮动分组后，选择top3，查看执行sql发现是count（）进行排序，取前三；有办法设置成sum（）嘛？
最近看过此主题的会员
25个回答
一点一点 发表于 2023-1-3 10:20
可以截图描述一下问题吗
SELECT *
FROM (
SELECT a.D3 AS D3
,a.E3 AS E3
,a.C3 AS C3
,COUNT(b.E3) AS E3_sortn
FROM (
SELECT a.D3 AS D3
,b.E3 AS E3
,a.C3 AS C3
FROM (
SELECT a.cust_id AS D3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
) a inner
JOIN (
SELECT CASE WHEN 1>2 THEN a.amt
WHEN 1>2 THEN a.amt/10000
ELSE a.amt
END AS E3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
) b
ON (a.C3 = b.C3)
) a
,(
SELECT a.D3 AS D3
,b.E3 AS E3
,a.C3 AS C3
FROM (
SELECT a.cust_id AS D3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
) a inner
JOIN (
SELECT CASE WHEN 1>2 THEN a.amt
WHEN 1>2 THEN a.amt/10000
ELSE a.amt
END AS E3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
) b
ON (a.C3 = b.C3)
) b
WHERE (a.E3 <= b.E3)
GROUP BY a.C3
,a.E3
,a.D3
HAVING (COUNT(b.E3) <= 3)
ORDER BY E3_sortn
) AS temp_
LIMIT 500000
一点一点 发表于 2023-1-3 11:08
您这边选择模式是只选择不分组。
改为常规分组试试呢
选择常规分组后生成的sql求了个max
SELECT *
FROM (
SELECT a.D3 AS D3
,a.E3 AS E3
,a.C3 AS C3
,COUNT(b.E3) AS E3_sortn
FROM (
SELECT a.D3 AS D3
,b.E3 AS E3
,a.C3 AS C3
FROM (
SELECT MAX(a.cust_id) AS D3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
GROUP BY a.cust_nm
) a inner
JOIN (
SELECT CASE WHEN 1>2 THEN a.amt
WHEN 1>2 THEN a.amt/10000
ELSE a.amt
END AS E3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
) b
ON (a.C3 = b.C3)
) a
,(
SELECT a.D3 AS D3
,b.E3 AS E3
,a.C3 AS C3
FROM (
SELECT MAX(a.cust_id) AS D3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
GROUP BY a.cust_nm
) a inner
JOIN (
SELECT CASE WHEN 1>2 THEN a.amt
WHEN 1>2 THEN a.amt/10000
ELSE a.amt
END AS E3
,a.cust_nm AS C3
FROM a_corp_cust_in_acct_track_camp a
WHERE ((a.sort_flag = 'DESC') AND (a.data_date = '20220531'))
) b
ON (a.C3 = b.C3)
) b
WHERE (a.E3 <= b.E3)
GROUP BY a.C3
,a.E3
,a.D3
HAVING (COUNT(b.E3) <= 3)
ORDER BY E3_sortn
) AS temp_
LIMIT 500000
张瑞阿 发表于 2023-1-3 11:21
选择常规分组后生成的sql求了个max
您是说D3引用的字段求了max是吗
您这个D3引用的字段类型是数值型还是字符型呢
字符型求max，数值型求sum
您可以修改字段类型试试
一点一点 发表于 2023-1-3 11:27
您是说D3引用的字段求了max是吗
您这个D3引用的字段类型是数值型还是字符型呢
字符型求max，数值型求sum
D3的引用字段求了max，但是排序字段选择的是E3（浮点型）分组字段是C3
