我有一個(gè)表，表名：table，欄位A,B,C；
有兩個(gè)語句如下：
SELECT SUM(A='a') FROM table WHERE conditon = 'x';
SELECT A,B,C FROM table WHERE conditon = 'x';因?yàn)閠able的數(shù)據(jù)量非常大(億級(jí))，單獨(dú)查詢這兩條語句耗費(fèi)的時(shí)間很大，大概需要4秒,因?yàn)檫@兩個(gè)語句的查詢表和條件一模一樣，所以我在想能不能只查一次，然后在通過某種處理，得到這兩個(gè)結(jié)果？
具體表結(jié)構(gòu)如下
CREATE TABLE "sor"."wpp_adefect_f" (
"glass_id" varchar(30) NOT NULL,
"evt_timestamp" timestamp(6) NOT NULL,
"panel_id" varchar(30) NOT NULL,
"defect_no" int4 NOT NULL,
"real_flg" varchar(1),
"ope_id" varchar(5),
"eqpt_id" varchar(20),
"jge_cnt" varchar(5) NOT NULL,
"unq_seq_id" varchar(25),
"defect_x_coord" varchar(8),
"defect_y_coord" varchar(8),
"data_no" varchar(5),
"gate_no" varchar(5),
"defect_flg" varchar(20),
"scan_slice" varchar(2),
"gray_type" varchar(4),
"defect_size" varchar(1),
"aoi_defect_area_size" varchar(6),
"aoi_defect_x_size" varchar(5),
"aoi_defect_y_size" varchar(5),
"aoi_defect_zone" varchar(1),
"adj_defect_x_size" varchar(5),
"adj_defect_y_size" varchar(5),
"adj_defect_zone" varchar(1),
"defect_area_size" varchar(6),
"defect_x_size" varchar(5),
"defect_y_size" varchar(5),
"defect_zong" varchar(1),
"defect_jge" varchar(2),
"defect_code" varchar(25),
"defect_img" varchar(40),
"dl_flg" varchar(1),
"defect_code_1" varchar(4),
"aoi_mura_jge" varchar(2),
"ibw_vcom_jge" varchar(2),
"aoi_defect_gray_max" varchar(3),
"aoi_defect_gray_min" varchar(3),
"aoi_defect_gray_avg" varchar(3),
"aoi_defect_gray_ref" varchar(3),
"adj_defect_gray_max" varchar(3),
"adj_defect_gray_min" varchar(3),
"adj_defect_gray_avg" varchar(3),
"adj_defect_gray_ref" varchar(3),
"defect_gray_max" varchar(3),
"defect_gray_min" varchar(3),
"defect_gray_avg" varchar(3),
"defect_gray_ref" varchar(3),
"adj_x_mura_val" varchar(4),
"adj_y_vcom_lvl" varchar(4),
"adj_area_size_vcom_val" varchar(5),
"adj_pix_x_coord" varchar(4),
"adj_pix_y_coord" varchar(4),
"measure_val" varchar(4),
"vcom_measure_lvl" varchar(4),
"insp_flg" varchar(9),
"alarm_tst_key_no" varchar(3),
"bef_tape_val" varchar(6),
"after_tape_val" varchar(6),
"aoi_defect_type" varchar(12),
"adj_jge_type" varchar(12),
"defect_rev_ibw_jge_type" varchar(12),
"tape_repair_type" varchar(12),
"ink_repair_type" varchar(12),
"ccd_id" varchar(2),
"aoi_defect_img_name" varchar(40),
"gray_img_name" varchar(40),
"adj_defect_img_name" varchar(40),
"bef_rvrp_img_name" varchar(40),
"aft_rvrp_img_name" varchar(40),
"bef_inrp_img_name" varchar(40),
"aft_inrp_img_name" varchar(40),
"evt_cate" varchar(4),
"evt_user" varchar(20),
"evt_note" varchar(60),
"def_vol_chg" varchar(6),
"line_avg_vol" varchar(6),
"test_def_type_1" varchar(4),
"test_def_type_2" varchar(2),
"rep_res" varchar(2),
"rep_def_code" varchar(4),
"rep_line_flg" varchar(1),
"def_retest_flg" varchar(1),
"def_layer" int4,
"lcvd_rep_time" float8,
"lcvd_length" int4,
"rep_def_type" varchar(12),
"def_img_name" varchar(19),
"analysis_code" varchar(12),
"rank" varchar(2),
"gray_level" varchar(6),
"nd_group" varchar(3),
"repair_line_no" varchar(1),
"pattern_code" varchar(3),
"pattern_name" varchar(20),
"eng_defect_name" varchar(20),
"cel_repair_defect_code" varchar(4),
"cel_repair_eng_defect_code" varchar(20),
"cel_repair_jge_code" varchar(2),
"light_on_block_id" varchar(2),
"defect_spare1" varchar(10),
"defect_spare2" varchar(10),
"cald_date_key_fk" int4,
"shf_key_fk" int4,
"hour_key_fk" int4,
"shop_key_fk" int4,
"prod_key_fk" int4,
"ope_key_fk" int4,
"eqpt_key_fk" int4,
"owner" varchar(4),
"lot_id" varchar(25),
"cald_date" date,
"shf_id" varchar(8),
"hour_id" int4,
"prod_id" varchar(25),
"shop_id" varchar(10),
"glass_type" varchar(5),
"proc_start_time" timestamp(0),
"proc_end_time" timestamp(0),
"pnl_grade" varchar(2),
"operator_id" varchar(12),
"operation_id" varchar(7),
"lcvd_type" varchar(12),
"reserved1" varchar(12),
"reserved2" varchar(12),
"reserved3" varchar(12),
"reserved4" varchar(12),
"etl_timestamp" timestamp(6),
"file_url" varchar(255),
"db_timestamp" timestamp(0) DEFAULT now(),
CONSTRAINT "wpp_adefect_f_pkey" PRIMARY KEY ("glass_id", "evt_timestamp", "panel_id", "defect_no", "jge_cnt")
)
WITH (OIDS=FALSE)
;
ALTER TABLE "sor"."wpp_adefect_f" OWNER TO "sys";
CREATE INDEX "adefect_f_indx1" ON "sor"."wpp_adefect_f" USING btree ("glass_id", "evt_timestamp");第一條查詢語句為
SELECT
glass_id,
evt_timestamp,
panel_id,
defect_no,
jge_cnt,
real_flg,
ope_id,
eqpt_id,
unq_seq_id,
defect_x_coord,
defect_y_coord,
data_no,
gate_no,
defect_flg,
defect_code,
defect_size,
prod_id,
defect_img,
operator_id,
lot_id,
shop_id,
(
CASE
WHEN SUBSTRING (insp_flg FROM 7 FOR 1) = 'C' THEN
'B'
WHEN SUBSTRING (insp_flg FROM 8 FOR 1) = 'T' THEN
'T'
WHEN SUBSTRING (insp_flg FROM 1 FOR 6) <> '******' THEN
'F'
ELSE
' '
END
) FBT_FLG,
defect_rev_ibw_jge_type,
aoi_defect_area_size,
defect_zong,
defect_x_size,
defect_y_size,
aoi_defect_type,
adj_jge_type,
gray_img_name,
aoi_defect_img_name,
adj_defect_img_name,
tape_repair_type,
ink_repair_type,
ccd_id,
scan_slice,
insp_flg,
adj_defect_gray_max,
adj_defect_gray_min,
adj_defect_gray_avg,
adj_defect_gray_ref,
adj_x_mura_val,
adj_y_vcom_lvl,
adj_area_size_vcom_val,
adj_defect_x_size,
adj_defect_y_size,
adj_defect_zone,
bef_rvrp_img_name,
aft_rvrp_img_name,
bef_inrp_img_name,
aft_inrp_img_name,
aoi_defect_gray_max,
aoi_defect_gray_min,
aoi_defect_gray_avg,
aoi_defect_gray_ref,
ROW_NUMBER () OVER (
ORDER BY
lpad(aoi_defect_area_size, 6, '0') DESC,
panel_id,
defect_no,
jge_cnt
) AS row_num
FROM
wpp_adefect_f
WHERE
1 = 1
AND (
(
1 = 1
AND evt_timestamp = '2018-03-08 14:13:00'
AND glass_id = 'A183100UAJ'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'ILDM06'
AND ope_id = 'A4311'
AND jge_cnt = '0'
)
OR (
1 = 1
AND evt_timestamp = '2018-03-08 14:12:28'
AND glass_id = 'A1831012CE'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'ILDM06'
AND ope_id = 'A4311'
AND jge_cnt = '0'
)
OR (
1 = 1
AND evt_timestamp = '2018-03-08 14:13:35'
AND glass_id = 'A183700CBK'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'IMRV02'
AND ope_id = 'A1853'
AND jge_cnt = '0'
)
OR (
1 = 1
AND evt_timestamp = '2018-03-08 14:13:47'
AND glass_id = 'A183700CAY'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'IMRV02'
AND ope_id = 'A1853'
AND jge_cnt = '0'
)
)
ORDER BY
lpad(aoi_defect_area_size, 6, '0') DESC,
panel_id,
defect_no,
jge_cnt第二條查詢語句為
SELECT
SUM (
CASE
WHEN defect_size = 'O'
AND SUBSTRING (defect_flg FROM 4 FOR 1) <> '1' THEN
1
ELSE
0
END
) AS O,
SUM (
CASE
WHEN defect_size = 'L'
AND SUBSTRING (defect_flg FROM 4 FOR 1) <> '1' THEN
1
ELSE
0
END
) AS L,
SUM (
CASE
WHEN defect_size = 'S'
AND SUBSTRING (defect_flg FROM 4 FOR 1) <> '1' THEN
1
ELSE
0
END
) AS S,
SUM (
CASE
WHEN defect_size = 'M'
AND SUBSTRING (defect_flg FROM 4 FOR 1) <> '1' THEN
1
ELSE
0
END
) AS M,
SUM (
CASE
WHEN SUBSTRING (defect_flg FROM 4 FOR 1) = '1' THEN
1
ELSE
0
END
) AS C
FROM
wpp_adefect_f
WHERE
1 = 1
AND (
(
1 = 1
AND evt_timestamp = '2018-03-08 14:13:00'
AND glass_id = 'A183100UAJ'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'ILDM06'
AND ope_id = 'A4311'
AND jge_cnt = '0'
)
OR (
1 = 1
AND evt_timestamp = '2018-03-08 14:12:28'
AND glass_id = 'A1831012CE'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'ILDM06'
AND ope_id = 'A4311'
AND jge_cnt = '0'
)
OR (
1 = 1
AND evt_timestamp = '2018-03-08 14:13:35'
AND glass_id = 'A183700CBK'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'IMRV02'
AND ope_id = 'A1853'
AND jge_cnt = '0'
)
OR (
1 = 1
AND evt_timestamp = '2018-03-08 14:13:47'
AND glass_id = 'A183700CAY'
AND prod_id = 'A1495A1ANK1'
AND eqpt_id = 'IMRV02'
AND ope_id = 'A1853'
AND jge_cnt = '0'
)
)
--你的想法是既要查出每條數(shù)據(jù)的詳情、又要求出某個(gè)字段的和
--下面是我的思路、你數(shù)據(jù)庫就只查這一條：
SELECT A,B,C FROM table WHERE conditon = 'x'
--求和的的話能在后臺(tái)用它查出的數(shù)據(jù)、通過循環(huán)方法得到
--但是還是會(huì)花上一段時(shí)間、只是不用重復(fù)寫這兩句SQL了
--如果你sql有 ROLLUP 這個(gè)方法那下面的 sql 適用
SELECT
Id, --你表的主鍵或者區(qū)分其它數(shù)據(jù)的列
SUM(CAST(A AS money)) newA ,
SUM(CAST(B AS money)) newB ,
SUM(CAST(C AS money)) newC
FROM (
SELECT MONTH(conditon) A,B,C
FROM [3d_User] d
WHERE conditon = 'x'
) V
GROUP BT Id
WITH ROLLUP
方法二：
--這個(gè)方法適用于sqlserver，比較簡單
SELECT SUM(A='a'),sum(B='b'),sum(C='c') FROM table WHERE conditon = 'x'
UNION ALL
SELECT A,B,C FROM table WHERE conditon = 'x'
--此方法查詢的這三個(gè)字段必須一一對(duì)應(yīng)，求出的和會(huì)在表的最后一行
北大青鳥APTECH成立于1999年。依托北京大學(xué)優(yōu)質(zhì)雄厚的教育資源和背景，秉承“教育改變生活”的發(fā)展理念，致力于培養(yǎng)中國IT技能型緊缺人才，是大數(shù)據(jù)專業(yè)的國家
達(dá)內(nèi)教育集團(tuán)成立于2002年，是一家由留學(xué)海歸創(chuàng)辦的高端職業(yè)教育培訓(xùn)機(jī)構(gòu)，是中國一站式人才培養(yǎng)平臺(tái)、一站式人才輸送平臺(tái)。2014年4月3日在美國成功上市，融資1
北大課工場是北京大學(xué)校辦產(chǎn)業(yè)為響應(yīng)國家深化產(chǎn)教融合／校企合作的政策，積極推進(jìn)“中國制造2025”，實(shí)現(xiàn)中華民族偉大復(fù)興的升級(jí)產(chǎn)業(yè)鏈。利用北京大學(xué)優(yōu)質(zhì)教育資源及背
博為峰，中國職業(yè)人才培訓(xùn)領(lǐng)域的先行者
曾工作于聯(lián)想擔(dān)任系統(tǒng)開發(fā)工程師，曾在博彥科技股份有限公司擔(dān)任項(xiàng)目經(jīng)理從事移動(dòng)互聯(lián)網(wǎng)管理及研發(fā)工作，曾創(chuàng)辦藍(lán)懿科技有限責(zé)任公司從事總經(jīng)理職務(wù)負(fù)責(zé)iOS教學(xué)及管理工作。
浪潮集團(tuán)項(xiàng)目經(jīng)理。精通Java與.NET 技術(shù)， 熟練的跨平臺(tái)面向?qū)ο箝_發(fā)經(jīng)驗(yàn)，技術(shù)功底深厚。 授課風(fēng)格 授課風(fēng)格清新自然、條理清晰、主次分明、重點(diǎn)難點(diǎn)突出、引人入勝。
精通HTML5和CSS3；Javascript及主流js庫，具有快速界面開發(fā)的能力，對(duì)瀏覽器兼容性、前端性能優(yōu)化等有深入理解。精通網(wǎng)頁制作和網(wǎng)頁游戲開發(fā)。
具有10 年的Java 企業(yè)應(yīng)用開發(fā)經(jīng)驗(yàn)。曾經(jīng)歷任德國Software AG 技術(shù)顧問，美國Dachieve 系統(tǒng)架構(gòu)師，美國AngelEngineers Inc. 系統(tǒng)架構(gòu)師。
掃碼關(guān)注鍍金池微信號(hào)
手機(jī)訂課，優(yōu)惠多多