下面這些查詢能不能合并為一條？
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880047,2880048,2880016,2880006)
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880045,2880005)
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880034,2880033)
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880032,2880029)
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880012,2880014,2880046)
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880010,2880011)
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880009,2880013)
select sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880044)
資料表描述
資料庫中有一張表（A），存盤了機構號。但并不規則，如：
jgh
-----
2880047,2880048,2880016,2880006
-----
2880045,2880005
----
2880044
----
2880037
另外一張表（B）存盤了資料，但是機構號是規則的，如：
jgh ckye
------ ---
2880047 1
------ ---
2880048 1
----- ---
2880016 1
----- ---
2880006 1
----- ---
2880045 1
----- ---
2880044 1
----- ---
2880005 1
---- ---
2880037 1
我想，按A的機構號規則進行匯總
結果為：
機構號 余額
2880047,2880048,2880016,2880006 4
2880045,2880005 2
2880044 1
2880037 1
uj5u.com熱心網友回復：
看看這樣行不行，不行再改。第一種方法：
select '2880047,2880048,2880016,2880006',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880047,2880048,2880016,2880006)
UNION ALL
select '2880045,2880005',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880045,2880005)
UNION ALL
select '2880034,2880033',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880034,2880033)
UNION ALL
select '2880032,2880029',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880032,2880029)
UNION ALL
select '2880012,2880014,2880046',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880012,2880014,2880046)
UNION ALL
select '2880010,2880011',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880010,2880011)
UNION ALL
select '2880009,2880013',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880009,2880013)
UNION ALL
select '2880044',sum(ckye),sum(dkye),sum(blye),sum(jlr) from think_daily_report where jgh in (2880044) ;
第二種方法：
select '2880047,2880048,2880016,2880006',
sum(CASE WHEN jgh in (2880047,2880048,2880016,2880006) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880047,2880048,2880016,2880006) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880047,2880048,2880016,2880006) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880047,2880048,2880016,2880006) THEN jlr ELSE 0 END)
from think_daily_report UNION ALL
select '2880045,2880005',
sum(CASE WHEN jgh in (2880045,2880005) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880045,2880005) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880045,2880005) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880045,2880005) THEN jlr ELSE 0 END)
from think_daily_report UNION ALL
select '2880034,2880033',
sum(CASE WHEN jgh in (2880034,2880033) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880034,2880033) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880034,2880033) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880034,2880033) THEN jlr ELSE 0 END)
from think_daily_report UNION ALL
select '2880032,2880029',
sum(CASE WHEN jgh in (2880032,2880029) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880032,2880029) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880032,2880029) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880032,2880029) THEN jlr ELSE 0 END)
from think_daily_report UNION ALL
select '2880012,2880014,2880046',
sum(CASE WHEN jgh in (2880012,2880014,2880046) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880012,2880014,2880046) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880012,2880014,2880046) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880012,2880014,2880046) THEN jlr ELSE 0 END)
from think_daily_report UNION ALL
select '2880010,2880011',
sum(CASE WHEN jgh in (2880010,2880011) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880010,2880011) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880010,2880011) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880010,2880011) THEN jlr ELSE 0 END)
from think_daily_report UNION ALL
select '2880009,2880013',
sum(CASE WHEN jgh in (2880009,2880013) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880009,2880013) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880009,2880013) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880009,2880013) THEN jlr ELSE 0 END)
from think_daily_report UNION ALL
select '2880044',
sum(CASE WHEN jgh in (2880044) THEN ckye ELSE 0 END),
sum(CASE WHEN jgh in (2880044) THEN dkye ELSE 0 END),
sum(CASE WHEN jgh in (2880044) THEN blye ELSE 0 END),
sum(CASE WHEN jgh in (2880044) THEN jlr ELSE 0 END)
from think_daily_report ;
uj5u.com熱心網友回復：
union 起來，不就一條了/哈哈。我能想到的方法就是用程序或者自定義函式來寫，單純寫查詢陳述句，我感覺我不會。
uj5u.com熱心網友回復：
那用存盤程序怎么實作呢？uj5u.com熱心網友回復：
在線等
uj5u.com熱心網友回復：
大佬，在不？
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/205039.html
標籤：MySQL
上一篇：啟動hive的小問題
下一篇：ACCESS累計遞增求和