iT邦幫忙

0

請教各位IT幫高手SQL 報表輸出EXCEL

請教各位it幫高手sql 報表輸出excel
yu0901 2022-06-23 16:12:16 ‧ 1581 瀏覽

  • 分享至 

  • xImage

請教各位IT幫高手SQL 報表輸出EXCEL已將數據輸入至SQL DEMO
[https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=c2e3944e66acdb555daeaf54fb8bf7af](SQL DEMO)

目前小弟公司報表EXCEL格式
https://ithelp.ithome.com.tw/upload/images/20220623/20148637Z2AdkHEY7Q.png

請問這報表該如何著手設計?

看更多先前的討論...收起先前的討論...
窮嘶發發發 iT邦高手 1 級 ‧ 2022-06-24 09:46:27 檢舉
基本上這是統計表，也就是有一個總表
我們先不考慮正規化
這張總表每一筆紀錄至少要有以下幾個欄位
單號 接單者 維修地點 (店家 中心 到府 ) 保固與否(保內保外) 維修行為 (自用展示教學整新機) 客戶 日期
大致上這樣，然後才是SQL 查詢語法
窮嘶發發發 iT邦高手 1 級 ‧ 2022-06-24 09:55:59 檢舉
如果要拆表
可以分成以下幾個表
單號 前置單號 接單者 客戶 日期 費用 物料成本 工時 結案
單號 維修地點
單號 保固與否
單號 維修行為
大概這樣
yu0901 iT邦新手 4 級 ‧ 2022-06-24 10:16:49 檢舉
您好感謝
表內PK是MECHANIC_NAME (技師姓名)
只是我沒概念第一段語法該怎麼下?
似乎不是用維修單號查詢
阿里巴巴大俠 iT邦新手 3 級 ‧ 2022-06-24 10:54:19 檢舉
我覺得你應該先多練習基本語法，看下來你應該對SQL的語法還不熟
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

0
allenlwh
iT邦高手 1 級 ‧ 2022-06-24 14:44:37
最佳解答

提供一個方向給你參考看看

DECLARE  @Table AS TABLE
(
    MECHANIC_NAME NVARCHAR(50),[type] NVARCHAR(50)
	,tot int,m1 int,m2 int,m3 int,m4 int,m5 int,m6 int,m7 int,m8 int,m9 int,m10 int,m11 int,m12 int,sort int
)

--送件方式分類
declare @name nvarchar(50)
DECLARE MyCursor Cursor FOR 
	SELECT distinct SENDCHOICE_NAME FROM [WO]
Open MyCursor 

Fetch NEXT FROM MyCursor INTO @name
While (@@FETCH_STATUS <> -1)
BEGIN	
	insert into @Table
	SELECT [MECHANIC_NAME],[SENDCHOICE_NAME],count(*)
		,sum(case when month([RECEIVEDATE])=1 then 1 else 0 end) m1 ,sum(case when month([RECEIVEDATE])=2 then 1 else 0 end) m2
		,sum(case when month([RECEIVEDATE])=3 then 1 else 0 end) m3,sum(case when month([RECEIVEDATE])=4 then 1 else 0 end) m4
		,sum(case when month([RECEIVEDATE])=5 then 1 else 0 end) m5,sum(case when month([RECEIVEDATE])=6 then 1 else 0 end) m6
		,sum(case when month([RECEIVEDATE])=7 then 1 else 0 end) m7,sum(case when month([RECEIVEDATE])=8 then 1 else 0 end) m8
		,sum(case when month([RECEIVEDATE])=9 then 1 else 0 end) m9,sum(case when month([RECEIVEDATE])=10 then 1 else 0 end) m10
		,sum(case when month([RECEIVEDATE])=11 then 1 else 0 end) m11,sum(case when month([RECEIVEDATE])=12 then 1 else 0 end) m12
		,1
	  FROM [WO]
	  where SENDCHOICE_NAME=@name
	  group by [MECHANIC_NAME],[SENDCHOICE_NAME]

Fetch NEXT FROM MyCursor INTO @name
END

CLOSE MyCursor
DEALLOCATE MyCursor

--送件方式小計
insert into @Table
select MECHANIC_NAME,N'小計',sum(tot),sum(m1),sum(m2),sum(m3),sum(m4),sum(m5),sum(m6),sum(m7),sum(m8),sum(m9),sum(m10),sum(m11),sum(m12),10
from @Table
	where sort=1	
	group by MECHANIC_NAME

----保固內
insert into @Table
SELECT MECHANIC_NAME,N'保固內',count(*)
		,sum(case when month([RECEIVEDATE])=1 then 1 else 0 end) m1,sum(case when month([RECEIVEDATE])=2 then 1 else 0 end) m2
		,sum(case when month([RECEIVEDATE])=3 then 1 else 0 end) m3,sum(case when month([RECEIVEDATE])=4 then 1 else 0 end) m4
		,sum(case when month([RECEIVEDATE])=5 then 1 else 0 end) m5,sum(case when month([RECEIVEDATE])=6 then 1 else 0 end) m6
		,sum(case when month([RECEIVEDATE])=7 then 1 else 0 end) m7,sum(case when month([RECEIVEDATE])=8 then 1 else 0 end) m8
		,sum(case when month([RECEIVEDATE])=9 then 1 else 0 end) m9,sum(case when month([RECEIVEDATE])=10 then 1 else 0 end) m10
		,sum(case when month([RECEIVEDATE])=11 then 1 else 0 end) m11,sum(case when month([RECEIVEDATE])=12 then 1 else 0 end) m12		  
		,11
  FROM [WO] t0 
	left join [ASSET] t1 on t0.ASSET_ID=t1.ASSET_ID
	where [HAVEWARRANTY]='1'
	group by [MECHANIC_NAME]

--保固外
insert into @Table
SELECT MECHANIC_NAME,N'保固外',count(*)
		,sum(case when month([RECEIVEDATE])=1 then 1 else 0 end) m1,sum(case when month([RECEIVEDATE])=2 then 1 else 0 end) m2
		,sum(case when month([RECEIVEDATE])=3 then 1 else 0 end) m3,sum(case when month([RECEIVEDATE])=4 then 1 else 0 end) m4
		,sum(case when month([RECEIVEDATE])=5 then 1 else 0 end) m5,sum(case when month([RECEIVEDATE])=6 then 1 else 0 end) m6
		,sum(case when month([RECEIVEDATE])=7 then 1 else 0 end) m7,sum(case when month([RECEIVEDATE])=8 then 1 else 0 end) m8
		,sum(case when month([RECEIVEDATE])=9 then 1 else 0 end) m9,sum(case when month([RECEIVEDATE])=10 then 1 else 0 end) m10
		,sum(case when month([RECEIVEDATE])=11 then 1 else 0 end) m11,sum(case when month([RECEIVEDATE])=12 then 1 else 0 end) m12
		,12
  FROM [WO] t0 
	left join [ASSET] t1 on t0.ASSET_ID=t1.ASSET_ID
	where [HAVEWARRANTY]='0'
	group by [MECHANIC_NAME]

--保固小計
insert into @Table
select MECHANIC_NAME,N'小計',sum(tot),sum(m1),sum(m2),sum(m3),sum(m4),sum(m5),sum(m6),sum(m7),sum(m8),sum(m9),sum(m10),sum(m11),sum(m12)
		,20
from @Table
	where sort>10 and sort<20
	group by MECHANIC_NAME

--MACHINEUSE_NAME分類
DECLARE MyCursor Cursor FOR 
	select distinct MACHINEUSE_NAME from [ASSET]
Open MyCursor 

Fetch NEXT FROM MyCursor INTO @name
While (@@FETCH_STATUS <> -1)
BEGIN	
	insert into @Table
	SELECT MECHANIC_NAME,t1.MACHINEUSE_NAME,count(*)
		,sum(case when month([RECEIVEDATE])=1 then 1 else 0 end) m1,sum(case when month([RECEIVEDATE])=2 then 1 else 0 end) m2
		,sum(case when month([RECEIVEDATE])=3 then 1 else 0 end) m3,sum(case when month([RECEIVEDATE])=4 then 1 else 0 end) m4
		,sum(case when month([RECEIVEDATE])=5 then 1 else 0 end) m5,sum(case when month([RECEIVEDATE])=6 then 1 else 0 end) m6
		,sum(case when month([RECEIVEDATE])=7 then 1 else 0 end) m7,sum(case when month([RECEIVEDATE])=8 then 1 else 0 end) m8
		,sum(case when month([RECEIVEDATE])=9 then 1 else 0 end) m9,sum(case when month([RECEIVEDATE])=10 then 1 else 0 end) m10
		,sum(case when month([RECEIVEDATE])=11 then 1 else 0 end) m11,sum(case when month([RECEIVEDATE])=12 then 1 else 0 end) m12
		,21
	FROM [WO] t0 
		left join [ASSET] t1 on t0.ASSET_ID=t1.ASSET_ID
		where t1.MACHINEUSE_NAME=@name
		group by [MECHANIC_NAME],t1.MACHINEUSE_NAME

Fetch NEXT FROM MyCursor INTO @name
END

CLOSE MyCursor
DEALLOCATE MyCursor

--MACHINEUSE_NAME小計
insert into @Table
select MECHANIC_NAME,N'小計',sum(tot),sum(m1),sum(m2),sum(m3),sum(m4),sum(m5),sum(m6),sum(m7),sum(m8),sum(m9),sum(m10),sum(m11),sum(m12)
		,30
from @Table
	where sort=21
	group by MECHANIC_NAME

--總表
select MECHANIC_NAME as '人員名稱',[type] as '111年度',tot as '合計'
	,m1 as '一月',m2 as '二月',m3 as '三月',m4 as '四月',m5 as '五月',m6 as '六月'
	,m7 as '七月',m8 as '八月',m9 as '九月',m10 as '十月',m11 as '十一月',m12 as '十二月'
from @Table
order by MECHANIC_NAME,sort
登入發表回應
0
fowei
iT邦新手 5 級 ‧ 2022-06-23 17:07:20

這表只大概看的出來就王二, 李三, 每個月工作內容.
但報表不是要怎麼做. 而是需要什麼.

以這張大概能做的是. 相比上月.相比去年同期. 如果按件計酬.
然後各件依類型有不同抽成. 比如到府X300(車資)..等.

但看不出維修效率. 如果要進階.
應該要有. 完修率. 完修天數. 未完修率.
另外王二, 李三做完的件. 也要跟個人做MAPPING.
這樣王二做的在保內回來件數.保外回來件數.. 也可以比較.

SO.. 無法給予明確的回應..

看更多先前的回應...收起先前的回應...
yu0901 iT邦新手 4 級 ‧ 2022-06-23 17:27:00 檢舉

您好
這張報表無須撈完休率資料
https://ithelp.ithome.com.tw/upload/images/20220623/20148637oYUXV4h9KF.png

只是RECEIVEDATE欄位 資料格式datetime 有ERROR
您知道怎麼處理嗎?

allenlwh iT邦高手 1 級 ‧ 2022-06-23 17:33:50 檢舉

RECEIVEDATE欄位 資料格式datetime 有ERROR??

有什麼樣的錯誤？輸入格式未統一嗎？

yu0901 iT邦新手 4 級 ‧ 2022-06-23 17:50:57 檢舉

sorry
有空再幫我看下!tks
https://ithelp.ithome.com.tw/upload/images/20220623/20148637OaRtlBpdHa.png

純真的人 iT邦大師 1 級 ‧ 2022-06-23 18:07:44 檢舉

MSSQL 可以分 datetime 或者 date
沒有 datetime(10)

yu0901 iT邦新手 4 級 ‧ 2022-06-23 21:17:33 檢舉

不好意思
若有空的高手再幫我看看
因為想說資料建好方便高手給建議上圖EXCEL報表該怎麼拉~!
https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ddbc7c9e967782fee61cdb5e17a94956
https://ithelp.ithome.com.tw/upload/images/20220623/20148637uCrszRz6tt.jpg

rogeryao iT邦超人 8 級 ‧ 2022-06-23 21:26:21 檢舉

請善用Google 翻譯

純真的人 iT邦大師 1 級 ‧ 2022-06-23 22:13:50 檢舉

我覺得是基礎SQL要重練~寫法錯誤百出...
建立資料表、新增、修改、刪除的寫法要重練....

yu0901 iT邦新手 4 級 ‧ 2022-06-23 23:01:39 檢舉

SORRY
以修正完畢

登入發表回應
0
rogeryao
iT邦超人 8 級 ‧ 2022-06-24 14:31:03 
CREATE TABLE WO (
MECHANIC_NAME NVARCHAR(50),
ASSET_ID NVARCHAR(50) NULL,
STATE_NAME NVARCHAR(50) NULL,
SENDCHOICE_NAME NVARCHAR(50) NULL,
RECEIVEDATE DATETIME);

INSERT INTO WO (MECHANIC_NAME,ASSET_ID,STATE_NAME,SENDCHOICE_NAME,RECEIVEDATE) VALUES
(N'A',N'1',N'已完修',N'自行送件','20220601'),          
(N'B',N'2',N'已完修',N'自行寄件','20220602'),          
(N'C',N'3',N'已完修',N'派員收件','20220603'),          
(N'A',N'4',N'未完修',N'到府維修','20220604'),          
(N'B',N'5',N'已完修',N'自行送件','20220605'),          
(N'B',N'6',N'未完修',N'自行寄件','20220606'),          
(N'C',N'7',N'已完修',N'派員收件','20220607'),          
(N'C',N'8',N'已完修',N'到府維修','20220608'),          
(N'C',N'9',N'未完修',N'自行送件','20220609'),          
(N'C',N'10',N'未完修',N'自行寄件','20220610'),     
(N'C',N'11',N'未完修',N'派員收件','20220611'),     
(N'C',N'12',N'未完修',N'到府維修','20220612'),     
(N'C',N'13',N'未完修',N'自行送件','20220613'),     
(N'C',N'14',N'未完修',N'自行寄件','20220614'),     
(N'D',N'15',N'未完修',N'派員收件','20220615'),
--
(N'A',N'21',N'已完修',N'自行送件','20220701'),      
(N'A',N'22',N'已完修',N'自行寄件','20220702'),      
(N'A',N'23',N'已完修',N'派員收件','20220703'),      
(N'A',N'24',N'未完修',N'到府維修','20220704'),      
(N'A',N'25',N'已完修',N'自行送件','20220705'),      
(N'B',N'26',N'未完修',N'自行寄件','20220706'),      
(N'B',N'27',N'已完修',N'派員收件','20220707'),      
(N'B',N'28',N'已完修',N'到府維修','20220708'),      
(N'B',N'29',N'未完修',N'自行送件','20220709'),      
(N'B',N'30',N'未完修',N'自行寄件','20220710'),     
(N'C',N'31',N'未完修',N'派員收件','20220711'),     
(N'C',N'32',N'未完修',N'到府維修','20220712'),     
(N'C',N'33',N'未完修',N'自行送件','20220713'),     
(N'C',N'34',N'未完修',N'自行寄件','20220714'),     
(N'C',N'35',N'未完修',N'派員收件','20220715');

CREATE TABLE ASSET (
ASSET_ID NVARCHAR(50),
HAVEWARRANTY NVARCHAR(50) NULL,
BRAND_NAME NVARCHAR(50) NULL,
MACHINEUSE_NAME NVARCHAR(50) NULL);

INSERT INTO ASSET (ASSET_ID,HAVEWARRANTY,BRAND_NAME,MACHINEUSE_NAME) VALUES
(N'1',N'1',N'brother',N'展示機'),
(N'2',N'0',N'NCC',N'教學機'),
(N'3',N'1',N'X',N'活動用'),
(N'4',N'0',N'Y',N'整理用'),
(N'5',N'1',N'Y',N'展示機'),
(N'6',N'0',N'Z',N'教學機'),
(N'7',N'1',N'Z',N'活動用'),
(N'8',N'0',N'Z',N'整理用'),
(N'9',N'1',N'Z',N'展示機'),
(N'10',N'1',NULL,N'教學機'),
(N'11',N'0',NULL,N'活動用'),
(N'12',N'0',NULL,N'整理用'),
(N'13',N'1',N'brother',N'展示機'),
(N'14',N'0',N'NCC',N'教學機'),
--
(N'21',N'1',N'brother',N'展示機'),
(N'22',N'1',N'NCC',N'教學機'),
(N'23',N'1',N'X',N'活動用'),
(N'24',N'1',N'Y',N'整理用'),
(N'25',N'1',N'Y',N'展示機'),
(N'26',N'1',N'Z',N'教學機'),
(N'27',N'1',N'Z',N'活動用'),
(N'28',N'1',N'Z',N'整理用'),
(N'29',N'1',N'Z',N'展示機'),
(N'30',N'1',NULL,N'教學機'),
(N'31',N'1',NULL,N'活動用'),
(N'32',N'1',NULL,N'整理用'),
(N'33',N'1',N'brother',N'展示機'),
(N'34',N'1',N'NCC',N'教學機');

;WITH CTE_XX AS (
  SELECT A.*,B.HAVEWARRANTY,B.BRAND_NAME,B.MACHINEUSE_NAME,DATEPART(MONTH, RECEIVEDATE) AS 'RECEIVEDATE_M'
  FROM WO A
  LEFT JOIN ASSET B ON B.ASSET_ID = A.ASSET_ID
  WHERE RECEIVEDATE >='20220101' AND RECEIVEDATE < '20230101'
  AND MECHANIC_NAME = 'A'),
CTE_01 AS (
  -- 到府維修,自行送件,派員收件
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,SENDCHOICE_NAME AS 'ITEM',COUNT(1) AS 'COUNTER',
  1 AS 'GROUPNO'
  FROM CTE_XX
  WHERE SENDCHOICE_NAME IN (N'到府維修',N'自行送件',N'派員收件')
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M,SENDCHOICE_NAME),
CTE_02 AS (
  -- 保內
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,N'保固內' AS 'ITEM',
  SUM(CASE WHEN HAVEWARRANTY = '1' THEN 1 ELSE 0 END) AS 'COUNTER',
  2 AS 'GROUPNO'
  FROM CTE_XX
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M),
CTE_03 AS (
  -- 保外
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,N'保固外' AS 'ITEM',
  SUM(CASE WHEN HAVEWARRANTY = '0' THEN 1 ELSE 0 END) AS 'COUNTER',
  2 AS 'GROUPNO'
  FROM CTE_XX
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M),
CTE_04 AS (
  -- 活動用,展示機,教學機
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME AS 'ITEM',COUNT(1) AS 'COUNTER',
  3 AS 'GROUPNO'
  FROM CTE_XX
  WHERE MACHINEUSE_NAME IN (N'活動用',N'展示機',N'教學機')
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME),
CTE_05 AS (
  -- 整理用
  SELECT  MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME AS 'ITEM',COUNT(1) AS 'COUNTER',
  3 AS 'GROUPNO'
  FROM CTE_XX
  WHERE MACHINEUSE_NAME IN (N'整理用')
  GROUP BY MECHANIC_NAME,RECEIVEDATE_M,MACHINEUSE_NAME),
CTE_06 AS (
  SELECT *
  FROM CTE_01
  UNION ALL
  SELECT *
  FROM CTE_02
  UNION ALL
  SELECT *
  FROM CTE_03
  UNION ALL
  SELECT *
  FROM CTE_04
  UNION ALL
  SELECT *
  FROM CTE_05),
CTE_KK AS (
  SELECT MECHANIC_NAME,GROUPNO,ITEM,
  SUM(COUNTER) AS 'TOTAL',
  SUM(CASE WHEN RECEIVEDATE_M='1' THEN COUNTER ELSE 0 END) AS 'M01',
  SUM(CASE WHEN RECEIVEDATE_M='2' THEN COUNTER ELSE 0 END) AS 'M02',
  SUM(CASE WHEN RECEIVEDATE_M='3' THEN COUNTER ELSE 0 END) AS 'M03',
  SUM(CASE WHEN RECEIVEDATE_M='4' THEN COUNTER ELSE 0 END) AS 'M04',
  SUM(CASE WHEN RECEIVEDATE_M='5' THEN COUNTER ELSE 0 END) AS 'M05',
  SUM(CASE WHEN RECEIVEDATE_M='6' THEN COUNTER ELSE 0 END) AS 'M06',
  SUM(CASE WHEN RECEIVEDATE_M='7' THEN COUNTER ELSE 0 END) AS 'M07',
  SUM(CASE WHEN RECEIVEDATE_M='8' THEN COUNTER ELSE 0 END) AS 'M08',
  SUM(CASE WHEN RECEIVEDATE_M='9' THEN COUNTER ELSE 0 END) AS 'M09',
  SUM(CASE WHEN RECEIVEDATE_M='10' THEN COUNTER ELSE 0 END) AS 'M10',
  SUM(CASE WHEN RECEIVEDATE_M='11' THEN COUNTER ELSE 0 END) AS 'M11',
  SUM(CASE WHEN RECEIVEDATE_M='12' THEN COUNTER ELSE 0 END) AS 'M12'
  FROM CTE_06
  GROUP BY MECHANIC_NAME,GROUPNO,ITEM)

SELECT *
FROM (
  -- FOR GROUPNO = 1
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 1
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'小計 : GROUPNO = 1' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 1
    GROUP BY MECHANIC_NAME,GROUPNO
  -- FOR GROUPNO = 2
    UNION ALL
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 2
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'小計 : GROUPNO = 2' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 2
    GROUP BY MECHANIC_NAME,GROUPNO
  -- FOR GROUPNO = 3
    UNION ALL
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 3
    AND ITEM IN (N'活動用',N'展示機',N'教學機')
    UNION ALL
    SELECT *
    FROM CTE_KK
    WHERE GROUPNO = 3
    AND ITEM IN (N'整理用')
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'小計 : GROUPNO = 3 (不含整理用)' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 3
    AND ITEM IN (N'活動用',N'展示機',N'教學機')
    GROUP BY MECHANIC_NAME,GROUPNO
    UNION ALL
    SELECT MECHANIC_NAME,GROUPNO,N'合計 : GROUPNO = 3' AS ITEM,SUM(TOTAL) TOTAL,
    SUM(M01) AS M01,SUM(M02) AS M02,SUM(M03) AS M03,SUM(M04) AS M04,SUM(M05) AS M05,SUM(M06) AS M06,
    SUM(M07) AS M07,SUM(M08) AS M08,SUM(M09) AS M09,SUM(M10) AS M10,SUM(M11) AS M11,SUM(M12) AS M12
    FROM CTE_KK
    WHERE GROUPNO = 3
    GROUP BY MECHANIC_NAME,GROUPNO
) AS A
ORDER BY A.MECHANIC_NAME,A.GROUPNO,
CASE WHEN LEFT(A.ITEM,4) = N'合計' THEN 99 
WHEN LEFT(A.ITEM,4) = N'小計' THEN 98 ELSE 1 END

Demo

建議 : 先學好基礎的 SQL 語法，否則 SQL 指令給你也是看不懂。
請參閱 : SQL語法教學 - 1Keydata，SQL 教學 - SQL 語法教學 Tutorial

yu0901 iT邦新手 4 級 ‧ 2022-06-24 15:09:25 檢舉

恐龍大
非常感謝您
SQL語法我看得懂但無法從無到有生出來沒有程式架構基礎說來汗顏我搞IT也搞了近20年之前主要熟悉系統及網路類似SI系統整合
我會好好精進SQL語法畢竟還有家庭要顧,IT邦幫忙是個很好的群,台灣不大搞IT的群範圍更小大夥兒技術交流非常良善!由衷感謝

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙