怎麼欄位的它牌會消失了?
語法哪裡錯了?
select g.維修人員,g.接單數,g.保固內,g.保固外,h.它牌 from (
select e.維修人員,e.接單數,e.保固內,f.保固外 from (
select c. 維修人員,c.接單數,d.保固內 from (
select a.維修人員 維修人員,b.接單數 from (
select distinct wo.MECHANIC_NAME 維修人員 from WO
group by MECHANIC_NAME
)a Left join (
select distinct wo.MECHANIC_NAME 維修人員,count(*) 接單數 from WO
group by wo.MECHANIC_NAME
)b ON (A.維修人員 = b.維修人員)
--需確認 ASSET.HAVEWARRANTY=1 (保內還是外)
)c left join (
select distinct wo.MECHANIC_NAME 維修人員,count(ASSET.HAVEWARRANTY) 保固內 from ASSET
join wo on (wo.ASSET_ID = ASSET.ASSET_ID)
where ASSET.HAVEWARRANTY = 1
group by wo.MECHANIC_NAME
)d on (c.維修人員 = d.維修人員)
)e left join (
select distinct wo.MECHANIC_NAME 維修人員,count(ASSET.HAVEWARRANTY) 保固外 from ASSET
join wo on (wo.ASSET_ID = ASSET.ASSET_ID)
where ASSET.HAVEWARRANTY = 0
group by wo.MECHANIC_NAME
)f on (e.維修人員 = f.維修人員)
)g left join( --它牌
select wo.MECHANIC_NAME 維修人員,count(ASSET.BRAND_NAME) 它牌 from ASSET
join wo on (wo.ASSET_ID = ASSET.ASSET_ID)
where (ASSET.BRAND_NAME NOT IN ('brother', 'NCC'))
group by wo.MECHANIC_NAME
)h on (g.維修人員 = h.維修人員)
這裡欄位它牌
下面再加一段sql指令它牌欄位就不見了
select i.維修人員,i.接單數,i.保固內,i.保固外,j.已完修數 from (
select g.維修人員,g.接單數,g.保固內,g.保固外,h.它牌 from (
select e.維修人員,e.接單數,e.保固內,f.保固外 from (
select c. 維修人員,c.接單數,d.保固內 from (
select a.維修人員 維修人員,b.接單數 from (
select distinct wo.MECHANIC_NAME 維修人員 from WO
group by MECHANIC_NAME
)a Left join (
select distinct wo.MECHANIC_NAME 維修人員,count(*) 接單數 from WO
group by wo.MECHANIC_NAME
)b ON (A.維修人員 = b.維修人員)
--需確認 ASSET.HAVEWARRANTY=1 (保內還是外)
)c left join (
select distinct wo.MECHANIC_NAME 維修人員,count(ASSET.HAVEWARRANTY) 保固內 from ASSET
join wo on (wo.ASSET_ID = ASSET.ASSET_ID)
where ASSET.HAVEWARRANTY = 1
group by wo.MECHANIC_NAME
)d on (c.維修人員 = d.維修人員)
)e left join (
select distinct wo.MECHANIC_NAME 維修人員,count(ASSET.HAVEWARRANTY) 保固外 from ASSET
join wo on (wo.ASSET_ID = ASSET.ASSET_ID)
where ASSET.HAVEWARRANTY = 0
group by wo.MECHANIC_NAME
)f on (e.維修人員 = f.維修人員)
)g left join( --它牌
select wo.MECHANIC_NAME 維修人員,count(ASSET.BRAND_NAME) 它牌 from ASSET
join wo on (wo.ASSET_ID = ASSET.ASSET_ID)
where (ASSET.BRAND_NAME NOT IN ('brother', 'NCC'))
group by wo.MECHANIC_NAME
)h on (g.維修人員 = h.維修人員)
)i left join (
select wo.MECHANIC_NAME 維修人員,count(wo.STATE_NAME) 已完修數 from WO
where wo.STATE_NAME like N'%已完修%'
group by MECHANIC_NAME
)j on (i.維修人員 = j.維修人員)
麻煩各位大神語法錯在哪?
已邀請的邦友 {{ invite_list.length }}/5
CREATE TABLE WO (
MECHANIC_NAME NVARCHAR(50),
ASSET_ID NVARCHAR(50) NULL,
STATE_NAME NVARCHAR(50) NULL);
INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME) VALUES
(N'A',N'1',N'已完修'),
(N'B',N'2',N'已完修'),
(N'C',N'3',N'已完修'),
(N'A',N'4',N'未完修'),
(N'B',N'5',N'已完修'),
(N'B',N'6',N'未完修'),
(N'C',N'7',N'已完修'),
(N'C',N'8',N'已完修'),
(N'C',N'9',N'未完修'),
(N'C',N'10',N'未完修'),
(N'C',N'11',N'未完修'),
(N'C',N'12',N'未完修'),
(N'C',N'13',N'未完修'),
(N'C',N'14',N'未完修'),
(N'D',N'15',N'未完修');
CREATE TABLE ASSET (
ASSET_ID NVARCHAR(50),
HAVEWARRANTY NVARCHAR(50) NULL,
BRAND_NAME NVARCHAR(50) NULL);
INSERT INTO ASSET (ASSET_ID,HAVEWARRANTY,BRAND_NAME) VALUES
(N'1',N'1',N'brother'),
(N'2',N'0',N'NCC'),
(N'3',N'1',N'X'),
(N'4',N'0',N'Y'),
(N'5',N'1',N'Y'),
(N'6',N'0',N'Z'),
(N'7',N'1',N'Z'),
(N'8',N'0',N'Z'),
(N'9',N'1',N'Z'),
(N'10',N'1',NULL),
(N'11',N'0',NULL),
(N'12',N'0',NULL),
(N'13',N'1',N'brother'),
(N'14',N'0',N'NCC');
SELECT A.MECHANIC_NAME AS '維修人員',
COUNT(*) AS '接單數',
SUM(CASE WHEN B.HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS '保固內',
SUM(CASE WHEN B.HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS '保固外',
SUM(CASE WHEN A.STATE_NAME like N'%已完修%' THEN 1 ELSE 0 END) AS '已完修',
SUM(CASE WHEN B.ASSET_ID IS NOT NULL
AND ISNULL(B.BRAND_NAME,'BRAND NAME IS NULL') NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS '它牌',
SUM(CASE WHEN B.BRAND_NAME NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS '它牌(不含 NULL))'
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
GROUP BY A.MECHANIC_NAME
建議 : 下次提問時請先建立測試資料，以免每個人都要建一次或是會錯意。
好強!感謝
請問若需加上公式
((已完修*100+已退修)/接單數)100 as 完成率
該如何下指引方向?
CONVERT(VARCHAR,CONVERT(DECIMAL(5,0),100.0 * ((m.已完修數*100.0+n.已退修數)/m.接單數) / 100)) + '%' AS 完成率 from (
CREATE TABLE WO (
MECHANIC_NAME NVARCHAR(50),
ASSET_ID NVARCHAR(50) NULL,
STATE_NAME NVARCHAR(50) NULL);
INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME) VALUES
(N'A',N'1',N'已完修'),
(N'B',N'2',N'已完修'),
(N'C',N'3',N'已完修'),
(N'A',N'4',N'未完修'),
(N'B',N'5',N'已完修'),
(N'B',N'6',N'未完修'),
(N'C',N'7',N'已退修'),
(N'C',N'8',N'已退修'),
(N'C',N'9',N'已退修'),
(N'C',N'10',N'未完修'),
(N'C',N'11',N'未完修'),
(N'C',N'12',N'未完修'),
(N'C',N'13',N'未完修'),
(N'C',N'14',N'未完修'),
(N'D',N'15',N'未完修');
SELECT MECHANIC_NAME AS '維修人員',TOTAL AS '接單數',
HAVEWARRANTY_Y AS '保固內',
HAVEWARRANTY_N AS '保固外',
FIX_FINISH AS '已完修',
FIX_RETURN AS '已退修',
BRAND AS '它牌',
BRAND_2 AS '它牌(不含 NULL))',
CONVERT(VARCHAR,CONVERT(DECIMAL(6,2),100.00 * (FIX_FINISH * 1.00 + FIX_RETURN * 1.00) / TOTAL * 1.00)) + '%' AS '完成率'
FROM (
SELECT A.MECHANIC_NAME,
COUNT(*) AS 'TOTAL',
SUM(CASE WHEN B.HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS 'HAVEWARRANTY_Y',
SUM(CASE WHEN B.HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS 'HAVEWARRANTY_N',
SUM(CASE WHEN A.STATE_NAME like N'%已完修%' THEN 1 ELSE 0 END) AS 'FIX_FINISH',
SUM(CASE WHEN A.STATE_NAME like N'%已退修%' THEN 1 ELSE 0 END) AS 'FIX_RETURN',
SUM(CASE WHEN B.ASSET_ID IS NOT NULL
AND ISNULL(B.BRAND_NAME,'BRAND NAME IS NULL') NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS 'BRAND',
SUM(CASE WHEN B.BRAND_NAME NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS 'BRAND_2'
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
GROUP BY A.MECHANIC_NAME
) AS C
太強了!
請問如何學好SQL語法
有無建議?
您好若您有空請幫我看這CASE最後一道題目
我真不知該塞哪裡感謝你願意分享知識
)o left join ( --時效達成數
select wo.MECHANIC_NAME 維修人員 ,count(*) 達成數 from WO
where (wo.REGISTERDATE) >= '20220101' AND (wo.REGISTERDATE) <= '20220615'
and wo.CLOSEDDATE <= DATEADD(day, 5, wo.REGISTERDATE)
group by MECHANIC_NAME
)p on (o.維修人員 = p.維修人員)
CREATE TABLE WO (
MECHANIC_NAME NVARCHAR(50),
ASSET_ID NVARCHAR(50) NULL,
STATE_NAME NVARCHAR(50) NULL,
REGISTERDATE DATE,
CLOSEDDATE DATE);
INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME,REGISTERDATE,CLOSEDDATE) VALUES
(N'A',N'1',N'已完修','20220601','20220602'),
(N'B',N'2',N'已完修','20220602','20220603'),
(N'C',N'3',N'已完修','20220603','20220604'),
(N'A',N'4',N'未完修','20220604',NULL),
(N'B',N'5',N'已完修','20220605','20220606'),
(N'B',N'6',N'未完修','20220606',NULL),
(N'C',N'7',N'已退修','20220607','20220608'),
(N'C',N'8',N'已退修','20220608','20220609'),
(N'C',N'9',N'已退修','20220609','20220610'),
(N'C',N'10',N'未完修','20220610',NULL),
(N'C',N'11',N'未完修','20220611',NULL),
(N'C',N'12',N'已完修','20220612','20220613'),
(N'C',N'13',N'已完修','20220613','20220619'),
(N'C',N'14',N'已完修','20220614','20220711'),
(N'D',N'15',N'已完修','20220615','20220712');
SELECT MECHANIC_NAME AS '維修人員',TOTAL AS '接單數',
HAVEWARRANTY_Y AS '保固內',
HAVEWARRANTY_N AS '保固外',
FIX_FINISH AS '已完修',
FIX_RETURN AS '已退修',
BRAND AS '它牌',
BRAND_2 AS '它牌(不含 NULL))',
CONVERT(VARCHAR,CONVERT(DECIMAL(6,2),100.00 * (FIX_FINISH * 1.00 + FIX_RETURN * 1.00) / TOTAL * 1.00)) + '%' AS '完成率',
DAY_EFFECTIVE AS '達成數',
CONVERT(VARCHAR,CONVERT(DECIMAL(6,2),100.00 * DAY_EFFECTIVE / TOTAL * 1.00)) + '%' AS '達成率'
FROM (
SELECT A.MECHANIC_NAME,
COUNT(*) AS 'TOTAL',
SUM(CASE WHEN B.HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS 'HAVEWARRANTY_Y',
SUM(CASE WHEN B.HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS 'HAVEWARRANTY_N',
SUM(CASE WHEN A.STATE_NAME like N'%已完修%' THEN 1 ELSE 0 END) AS 'FIX_FINISH',
SUM(CASE WHEN A.STATE_NAME like N'%已退修%' THEN 1 ELSE 0 END) AS 'FIX_RETURN',
SUM(CASE WHEN B.ASSET_ID IS NOT NULL
AND ISNULL(B.BRAND_NAME,'BRAND NAME IS NULL') NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS 'BRAND',
SUM(CASE WHEN B.BRAND_NAME NOT IN ('brother', 'NCC') THEN 1 ELSE 0 END) AS 'BRAND_2',
SUM(CASE WHEN A.CLOSEDDATE <= DATEADD(day, 5, A.REGISTERDATE) THEN 1 ELSE 0 END) AS 'DAY_EFFECTIVE'
FROM WO A
LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
WHERE A.REGISTERDATE >='20220601' AND A.REGISTERDATE <='20220614'
GROUP BY A.MECHANIC_NAME
) AS C
非常感謝您
我是搞系統和網路出生的linux和sun
換工作公司需要硬著頭皮上
確實差很多
真的自己也該多努力
認真來說，我一直看不懂你接那麼多子查尋是要幹啥的。
看起來也不是為了排序，也不是為了群組化。
感覺很多餘。(雖然我沒很詳細全看完)
再來，就是中文名欄位。雖然說目前的SQL都可以支援中文名欄位。
但難保不會發生問題。
我之前就幫人解決過，因為中文名欄位，造成變數過大問題。
他也是跟你一樣用了非常多的JOIN及中文欄位名。但在傳送對應變數。發生了SQL指令長度過大的問題。
原因是實際上SQL運行到中文欄位時，其實會做編碼化處理。
這會導制原本的中文名長度會增加6~10倍的長度。
進而導致發生SQL命令長度過長無法執行的問題。
當然了，其原因也是因為它下了非常長的語法造成的。裏面將近有400多個中文字在SQL碼上。
才會發生這樣的事。所以一般我都會教導不要用中文欄位名。
但輸出中文欄位名其實是沒關係的。畢竟輸出的話。只在SELECT上，下「test AS 測試」
就可以將原本英文欄位名輸出為中文了。
iThome鐵人賽
看影片追技術