SQL如何根据作业量的大小取大值的工班代码并且sum两条记录的作业量,最后有同名的显示一条记录

u010133107 2017-07-26 02:23:11

如标题。
现在表的一天数据如图

图片中出现：同一人名同一天有两条数据，一条NS，一条DS；

需求就是：当出现上述情况时，使用两个条件判断，一个是SIZE_20_VOLUME, 一个是UNIT_TOTAL_VOLUME

当一个条件有相等的值，则用另个条件判断。判断值的大小，取大值的workshiftcode。

然后将这两条数据的 size_20_volume，Size_40_Volume，Size_45_Volume，SP_Cntr_Volume，Hatch_Quantity，UNIT_Total_Volume 列相加合并，得到跟其他人员一样的一条数据。

为什么需要两个条件判断：是因为又出现过值相等的情况。

使用过表值函数和 cross apply 但就是因为有出现值相等的情况导致返回两个值的workshiftcode

如果写游标，应该怎么写？

...全文

454 24 打赏收藏转发到动态举报

写回复

用AI写文章

24 条回复

切换为时间正序

请发表友善的回复…

发表回复

u010133107 2017-08-02

打赏
举报

知道原因了。谢谢各位大侠的无私奉献。解决了。结帖发分！

u010133107 2017-08-02

打赏
举报

引用 20 楼 sinat_28984567 的回复:

[quote=引用 19 楼 u010133107 的回复:] [quote=引用 18 楼 sinat_28984567 的回复:] 楼主我用的就是你给的那个数据，试着没问题；

SELECT * FROM [InternalDriverPieceWork] WHERE WorkshiftDate='2017-07-03' AND InternalDriverName='陈尔鸿'

SELECT  a.InternalDriverName ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN ISNULL(a.[SIZE_20_VOLUME], 0) = ISNULL(b.[SIZE_20_VOLUME],
                                                             0)
                 THEN ( CASE WHEN ISNULL(a.[UNIT_TOTAL_VOLUME], 0) > ISNULL(b.[UNIT_TOTAL_VOLUME],
                                                              0)
                             THEN a.[workshiftcode]
                             ELSE ISNULL(b.[workshiftcode], a.[workshiftcode])
                        END )
                 WHEN ISNULL(a.[SIZE_20_VOLUME], 0) > ISNULL(b.[SIZE_20_VOLUME],
                                                             0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    [InternalDriverPieceWork] a
        LEFT JOIN [InternalDriverPieceWork] b ON b.InternalDriverName = a.InternalDriverName
                                                 AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                                       OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                                                     )
WHERE   a.WorkshiftDate = '2017-07-03'
        AND a.InternalDriverName = '陈尔鸿'
GROUP BY a.InternalDriverName

合成一条数据的显示还是要显示所有字段，不能单单只显示SIZE_20_VOLUME，UNIT_TOTAL_VOLUME，workshiftcode。还需要其他字段都显示出来。[/quote] 是，主要是图片不好复制代码，所以就简单的写了两个字段的，但是就这两个字段也没有问题……[/quote] sinat_28984567 您再看下，测试数据是没问题；但是同一名字的，添加了不同workshiftdate的话（例如添加7月9号两条数据），根据您的语句查询出来值就错。

u010133107 2017-08-01

打赏
举报

奇怪了，sinat_28984567 使用我给的数据，为什么我得出的结果会不一样？查询单个人的时候，都是一样，合并结果时，使用sinat_28984567 的语句就不一样呢？

道素 2017-07-31

打赏
举报

引用 4 楼 u010133107 的回复:

[quote=引用 3 楼 ch21st 的回复:]


if not object_id(N'Tempdb..#t') is null drop table #t

Create table #t(drivername nvarchar(10),driverid varchar(20),workshiftcode varchar(2),size_20_volume int
                ,Size_40_Volume int,Size_45_Volume int,SP_Cntr_Volume int,Hatch_Quantity int,UNIT_Total_Volume int)

Insert into #t
select N'陈火','chenhuo','NS',0,5,0,0,0,4 union all
select N'陈火城','chenhuocheng','NS',0,4,0,0,0,4 union all
select N'陈火城','chenhuocheng','DS',0,3,0,0,0,9
select * from (
    
   select drivername,driverid,workshiftcode
        ,sum(size_20_volume)over(partition by drivername,driverid) as size_20_volume
        ,sum(Size_40_Volume)over(partition by drivername,driverid) as Size_40_Volume
        ,sum(Size_45_Volume)over(partition by drivername,driverid) as Size_45_Volume
        ,sum(SP_Cntr_Volume)over(partition by drivername,driverid) as SP_Cntr_Volume
        ,sum(Hatch_Quantity)over(partition by drivername,driverid) as Hatch_Quantity
        ,sum(UNIT_Total_Volume)over(partition by drivername,driverid) as UNIT_Total_Volume
  ,row_number()over(partition by drivername,driverid order by size_20_volume desc,UNIT_Total_Volume desc) as rn from #t
) as t where t.rn=1


  	drivername	driverid	workshiftcode	size_20_volume	Size_40_Volume	Size_45_Volume	SP_Cntr_Volume	Hatch_Quantity	UNIT_Total_Volume
1	陈火	chenhuo	NS	0	5	0	0	0	4
2	陈火城	chenhuocheng	NS	0	4	0	0	0	4
3	陈火城	chenhuocheng	DS	0	3	0	0	0	9
结果
  	drivername	driverid	workshiftcode	size_20_volume	Size_40_Volume	Size_45_Volume	SP_Cntr_Volume	Hatch_Quantity	UNIT_Total_Volume	rn
1	陈火	chenhuo	NS	0	5	0	0	0	4	1
2	陈火城	chenhuocheng	DS	0	7	0	0	0	13	1

试了这个，在where 后面加上了具体的日期，查询结果发现数据量减少了，3号不止8个人又作业。且值也不对。

[/quote] 你要把条件放在里边的子查询，我写的逻辑是处理最后的结果，已经过滤掉一些数据，你在这里去查询肯定结果不对

u010133107 2017-07-28

打赏
举报

引用 16 楼 sinat_28984567 的回复:

[quote=引用 15 楼 u010133107 的回复:]
[quote=引用 13 楼 u010133107 的回复:]
[quote=引用 10 楼 sinat_28984567 的回复:]
看看是这个意思吗？

--测试数据

if not object_id(N'Tempdb..#T') is null

    drop table #T

Go

Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] NVARCHAR(100))

Insert #T

select 1,10,20,'DS' union all

select 1,20,20,'NS' union all

select 2,20,30,'DS' union all

select 2,20,20,'NS' union all

select 3,20,20,'DS'

Go

--测试数据结束

SELECT  a.name ,

        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,

        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,

        MAX(CASE WHEN a.[SIZE_20_VOLUME] = ISNULL(b.[SIZE_20_VOLUME],0)

                 THEN ( CASE WHEN a.[UNIT_TOTAL_VOLUME] > ISNULL(b.[UNIT_TOTAL_VOLUME],0)

                             THEN a.[workshiftcode]

                             ELSE b.[workshiftcode]

                        END )

                 WHEN a.[SIZE_20_VOLUME] > ISNULL(b.[SIZE_20_VOLUME],0)

                 THEN a.[workshiftcode]

                 ELSE b.[workshiftcode]

            END) AS [workshiftcode]

FROM    #T a

        LEFT JOIN #T b ON b.name = a.name

                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME

                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME

                              )

GROUP BY a.name

是这意思。但是为什么我查询某一天的时候量都偏大？[/quote]

好像又发现个问题，语句对一天只出现一次的，且SIZE_20_VOLUME 和UNIT_TOTAL_VOLUME 都无值的，查询不出workshiftcode的值。[/quote]
恩这里有点问题，试试这个

--测试数据

if not object_id(N'Tempdb..#T') is null

    drop table #T

Go

Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] NVARCHAR(100),[WorkshiftDate] [date])

Insert #T

select 1,10,20,'DS','2017-07-01' union all

select 1,20,20,'NS','2017-07-01' union all

select 2,20,30,'DS','2017-07-01' union all

select 2,20,20,'NS','2017-07-01' union all

select 3,NULL,NULL,'DS','2017-07-01'

Go

--测试数据结束

SELECT  a.name ,

        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,

        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,

        MAX(CASE WHEN ISNULL(a.[SIZE_20_VOLUME],0) = ISNULL(b.[SIZE_20_VOLUME],0)

                 THEN ( CASE WHEN ISNULL(a.[UNIT_TOTAL_VOLUME],0) > ISNULL(b.[UNIT_TOTAL_VOLUME],0)

                             THEN a.[workshiftcode]

                             ELSE ISNULL(b.[workshiftcode],a.[workshiftcode])

                        END )

                 WHEN ISNULL(a.[SIZE_20_VOLUME],0) > ISNULL(b.[SIZE_20_VOLUME],0)

                 THEN a.[workshiftcode]

                 ELSE b.[workshiftcode]

            END) AS [workshiftcode]

FROM    #T a

        LEFT JOIN #T b ON b.name = a.name

                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME

                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME

                              )

GROUP BY a.name

[/quote]
您好。真的太麻烦您了。但是这语句还是有问题。例如 3号的"陈尔鸿"，根据您的语句查询出来还是有两个问题：1）作业量明显多了。2） workshiftcode的值还是错了。如图：

二月十六版主 2017-07-28

打赏
举报

引用 15 楼 u010133107 的回复:

[quote=引用 13 楼 u010133107 的回复:] [quote=引用 10 楼 sinat_28984567 的回复:] 看看是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] NVARCHAR(100))
Insert #T
select 1,10,20,'DS' union all
select 1,20,20,'NS' union all
select 2,20,30,'DS' union all
select 2,20,20,'NS' union all
select 3,20,20,'DS'
Go
--测试数据结束
SELECT  a.name ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN a.[SIZE_20_VOLUME] = ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN ( CASE WHEN a.[UNIT_TOTAL_VOLUME] > ISNULL(b.[UNIT_TOTAL_VOLUME],0)
                             THEN a.[workshiftcode]
                             ELSE b.[workshiftcode]
                        END )
                 WHEN a.[SIZE_20_VOLUME] > ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    #T a
        LEFT JOIN #T b ON b.name = a.name
                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                              )
GROUP BY a.name

是这意思。但是为什么我查询某一天的时候量都偏大？[/quote] 好像又发现个问题，语句对一天只出现一次的，且SIZE_20_VOLUME 和UNIT_TOTAL_VOLUME 都无值的，查询不出workshiftcode的值。[/quote] 恩这里有点问题，试试这个

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] NVARCHAR(100),[WorkshiftDate] [date])
Insert #T
select 1,10,20,'DS','2017-07-01' union all
select 1,20,20,'NS','2017-07-01' union all
select 2,20,30,'DS','2017-07-01' union all
select 2,20,20,'NS','2017-07-01' union all
select 3,NULL,NULL,'DS','2017-07-01'
Go
--测试数据结束
SELECT  a.name ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN ISNULL(a.[SIZE_20_VOLUME],0) = ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN ( CASE WHEN ISNULL(a.[UNIT_TOTAL_VOLUME],0) > ISNULL(b.[UNIT_TOTAL_VOLUME],0)
                             THEN a.[workshiftcode]
                             ELSE ISNULL(b.[workshiftcode],a.[workshiftcode])
                        END )
                 WHEN ISNULL(a.[SIZE_20_VOLUME],0) > ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    #T a
        LEFT JOIN #T b ON b.name = a.name
                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                              )
GROUP BY a.name

u010133107 2017-07-28

打赏
举报

引用 13 楼 u010133107 的回复:

[quote=引用 10 楼 sinat_28984567 的回复:] 看看是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] NVARCHAR(100))
Insert #T
select 1,10,20,'DS' union all
select 1,20,20,'NS' union all
select 2,20,30,'DS' union all
select 2,20,20,'NS' union all
select 3,20,20,'DS'
Go
--测试数据结束
SELECT  a.name ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN a.[SIZE_20_VOLUME] = ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN ( CASE WHEN a.[UNIT_TOTAL_VOLUME] > ISNULL(b.[UNIT_TOTAL_VOLUME],0)
                             THEN a.[workshiftcode]
                             ELSE b.[workshiftcode]
                        END )
                 WHEN a.[SIZE_20_VOLUME] > ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    #T a
        LEFT JOIN #T b ON b.name = a.name
                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                              )
GROUP BY a.name

是这意思。但是为什么我查询某一天的时候量都偏大？[/quote] 好像又发现个问题，语句对一天只出现一次的，且SIZE_20_VOLUME 和UNIT_TOTAL_VOLUME 都无值的，查询不出workshiftcode的值。

二月十六版主 2017-07-28

打赏
举报

引用 13 楼 u010133107 的回复:

[quote=引用 10 楼 sinat_28984567 的回复:] 看看是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] NVARCHAR(100))
Insert #T
select 1,10,20,'DS' union all
select 1,20,20,'NS' union all
select 2,20,30,'DS' union all
select 2,20,20,'NS' union all
select 3,20,20,'DS'
Go
--测试数据结束
SELECT  a.name ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN a.[SIZE_20_VOLUME] = ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN ( CASE WHEN a.[UNIT_TOTAL_VOLUME] > ISNULL(b.[UNIT_TOTAL_VOLUME],0)
                             THEN a.[workshiftcode]
                             ELSE b.[workshiftcode]
                        END )
                 WHEN a.[SIZE_20_VOLUME] > ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    #T a
        LEFT JOIN #T b ON b.name = a.name
                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                              )
GROUP BY a.name

是这意思。但是为什么我查询某一天的时候量都偏大？[/quote] 量都偏大是什么意思？

u010133107 2017-07-28

打赏
举报

引用 10 楼 sinat_28984567 的回复:

看看是这个意思吗？

--测试数据
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] NVARCHAR(100))
Insert #T
select 1,10,20,'DS' union all
select 1,20,20,'NS' union all
select 2,20,30,'DS' union all
select 2,20,20,'NS' union all
select 3,20,20,'DS'
Go
--测试数据结束
SELECT  a.name ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN a.[SIZE_20_VOLUME] = ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN ( CASE WHEN a.[UNIT_TOTAL_VOLUME] > ISNULL(b.[UNIT_TOTAL_VOLUME],0)
                             THEN a.[workshiftcode]
                             ELSE b.[workshiftcode]
                        END )
                 WHEN a.[SIZE_20_VOLUME] > ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    #T a
        LEFT JOIN #T b ON b.name = a.name
                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                              )
GROUP BY a.name

是这意思。但是为什么我查询某一天的时候量都偏大？

u010133107 2017-07-28

打赏
举报

引用 11 楼 RINK_1 的回复:

[quote=引用 9 楼 u010133107 的回复:] USE [HyperCubeDW] GO /****** Object: Table [Operation].[InternalDriverPieceWork] Script Date: 2017/7/27 9:57:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Operation].[InternalDriverPieceWork]( [PieceWorkID] [int] IDENTITY(1,1) NOT NULL, [InternalDriverID] [nvarchar](10) NULL, [InternalDriverName] [nvarchar](20) NULL, [WeiXinID] [nvarchar](50) NULL, [WorkshiftDate] [date] NULL, [WorkshiftCode] [nvarchar](10) NULL, [Size_20_Volume] [int] NULL, [Size_40_Volume] [int] NULL, [Size_45_Volume] [int] NULL, [SP_Cntr_Volume] [int] NULL, [Hatch_Quantity] [int] NULL, [UNIT_Total_Volume] [int] NULL, CONSTRAINT [PK_InternalDriver_PieceWorkID] PRIMARY KEY CLUSTERED ( [PieceWorkID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [Operation].[InternalDriverPieceWork] ON INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14517, N'8009', N'陈绍伟', N'chenshaowei', CAST(N'2017-07-03' AS Date), N'NS', 92, 127, 0, 9, 0, 228) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14518, N'8240', N'伍梓鹏', N'wuzipeng', CAST(N'2017-07-03' AS Date), N'DS', 295, 40, 0, 0, 0, 335) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14519, N'8185', N'张文浩', N'zhangwenhao', CAST(N'2017-07-03' AS Date), N'NS', 134, 48, 0, 0, 0, 182) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14520, N'8124', N'邹国斌', N'zouguobin', CAST(N'2017-07-03' AS Date), N'DS', 224, 85, 0, 1, 0, 310) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14521, N'8029', N'张子龙', N'zhangzilong', CAST(N'2017-07-03' AS Date), N'DS', 142, 144, 0, 0, 0, 286) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14522, N'8096', N'郭程森', N'guochengsen', CAST(N'2017-07-03' AS Date), N'NS', 120, 53, 0, 8, 0, 181) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14523, N'8148', N'何桢', N'hezhen', CAST(N'2017-07-03' AS Date), N'DS', 308, 32, 0, 0, 0, 340) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14524, N'8150', N'罗志刚', N'luozhigang', CAST(N'2017-07-03' AS Date), N'NS', 290, 0, 0, 0, 0, 290) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14525, N'8215', N'黎乐彬', N'lilebin', CAST(N'2017-07-03' AS Date), N'NS', 126, 22, 0, 0, 0, 148) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14526, N'8058', N'郑继耿', N'zhengjigeng', CAST(N'2017-07-03' AS Date), N'NS', 105, 18, 0, 0, 0, 123) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14527, N'8219', N'林子杰', N'linzijie', CAST(N'2017-07-03' AS Date), N'NS', 186, 36, 0, 0, 0, 222) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14528, N'8126', N'陈智聪', N'chenzhicong', CAST(N'2017-07-03' AS Date), N'NS', 224, 64, 0, 8, 0, 296) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14529, N'8021', N'吴少俊', N'wushaojun', CAST(N'2017-07-03' AS Date), N'NS', 163, 94, 0, 0, 0, 257) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14530, N'8078', N'符华富', N'fuhuafu', CAST(N'2017-07-03' AS Date), N'DS', 195, 19, 0, 0, 0, 214) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14531, N'8101', N'朱慧安', N'zhuhuian', CAST(N'2017-07-03' AS Date), N'NS', 193, 45, 0, 0, 0, 238) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14532, N'8129', N'蔡钦滨', N'caiqinbin', CAST(N'2017-07-03' AS Date), N'DS', 171, 20, 0, 0, 0, 191) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14541, N'8129', N'蔡钦滨', N'caiqinbin', CAST(N'2017-07-03' AS Date), N'NS', 6, 0, 0, 0, 0, 6) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14542, N'8049', N'罗铭坚', N'luomingjian', CAST(N'2017-07-03' AS Date), N'DS', 90, 20, 0, 0, 0, 110) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14547, N'8320', N'谭炜坤', N'tanweikun', CAST(N'2017-07-03' AS Date), N'NS', 29, 31, 0, 3, 0, 63) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14548, N'8274', N'李繁鑫', NULL, CAST(N'2017-07-03' AS Date), N'NS', 86, 10, 0, 0, 0, 96) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14636, N'8255', N'陈尔鸿', N'chenerhong', CAST(N'2017-07-03' AS Date), N'NS', 6, 0, 0, 0, 0, 6) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14666, N'8255', N'陈尔鸿', N'chenerhong', CAST(N'2017-07-03' AS Date), N'DS', 39, 10, 0, 0, 0, 49) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14667, N'8313', N'陈伟斌', N'chenweibin', CAST(N'2017-07-03' AS Date), N'DS', 12, 0, 0, 0, 0, 12) GO SET IDENTITY_INSERT [Operation].[InternalDriverPieceWork] OFF 这是一天的数据的几个例子；里面有些人名有重复，需要根据使用两个条件判断，一个是SIZE_20_VOLUME, 一个是UNIT_TOTAL_VOLUME；当一个条件有相等的值，则用另个条件判断。判断值的大小，取大值的workshiftcode中的DS或者NS；然后合成一条数据。貌似二楼的语句比较符合，但是少了人员。

你明确你的统计逻辑是以下哪种。 1. 只统计同一个人在同一天出现2条记录，且workshiftcode分别是NS和DS的这种情况。那我的可以用。你根据特定人名查询没有记录，那估计是不满足前述这一条件。 2. 一个人在一天只出现1条记录，这种情况也纳入统计，那应该用sinat_28984567的10楼逻辑。他已经把情况1也考虑了，即使只统计情况1，也就略微加些Where限制条件。 3. 如果一个人同一天出现2条以上的记录，那你描述的逻辑就值得再讨论了。[/quote] 我想统计的是第二种，当一个人一天只出现1条记录这种也纳入统计。只有当一天中有一个人出现两条记录时就需要根据SIZE_20_VOLUME和UNIT_TOTAL_VOLUME 两个字段去判断，取大值的workshiftcode的值，合并成一条记录跟其他只出现一条记录的人员一起显示。

二月十六版主 2017-07-28

打赏
举报

引用 19 楼 u010133107 的回复:

[quote=引用 18 楼 sinat_28984567 的回复:] 楼主我用的就是你给的那个数据，试着没问题；

SELECT * FROM [InternalDriverPieceWork] WHERE WorkshiftDate='2017-07-03' AND InternalDriverName='陈尔鸿'

SELECT  a.InternalDriverName ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN ISNULL(a.[SIZE_20_VOLUME], 0) = ISNULL(b.[SIZE_20_VOLUME],
                                                             0)
                 THEN ( CASE WHEN ISNULL(a.[UNIT_TOTAL_VOLUME], 0) > ISNULL(b.[UNIT_TOTAL_VOLUME],
                                                              0)
                             THEN a.[workshiftcode]
                             ELSE ISNULL(b.[workshiftcode], a.[workshiftcode])
                        END )
                 WHEN ISNULL(a.[SIZE_20_VOLUME], 0) > ISNULL(b.[SIZE_20_VOLUME],
                                                             0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    [InternalDriverPieceWork] a
        LEFT JOIN [InternalDriverPieceWork] b ON b.InternalDriverName = a.InternalDriverName
                                                 AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                                       OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                                                     )
WHERE   a.WorkshiftDate = '2017-07-03'
        AND a.InternalDriverName = '陈尔鸿'
GROUP BY a.InternalDriverName

合成一条数据的显示还是要显示所有字段，不能单单只显示SIZE_20_VOLUME，UNIT_TOTAL_VOLUME，workshiftcode。还需要其他字段都显示出来。[/quote] 是，主要是图片不好复制代码，所以就简单的写了两个字段的，但是就这两个字段也没有问题……

u010133107 2017-07-28

打赏
举报

引用 18 楼 sinat_28984567 的回复:

楼主我用的就是你给的那个数据，试着没问题；

SELECT * FROM [InternalDriverPieceWork] WHERE WorkshiftDate='2017-07-03' AND InternalDriverName='陈尔鸿'

SELECT  a.InternalDriverName ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN ISNULL(a.[SIZE_20_VOLUME], 0) = ISNULL(b.[SIZE_20_VOLUME],
                                                             0)
                 THEN ( CASE WHEN ISNULL(a.[UNIT_TOTAL_VOLUME], 0) > ISNULL(b.[UNIT_TOTAL_VOLUME],
                                                              0)
                             THEN a.[workshiftcode]
                             ELSE ISNULL(b.[workshiftcode], a.[workshiftcode])
                        END )
                 WHEN ISNULL(a.[SIZE_20_VOLUME], 0) > ISNULL(b.[SIZE_20_VOLUME],
                                                             0)
                 THEN a.[workshiftcode]
                 ELSE b.[workshiftcode]
            END) AS [workshiftcode]
FROM    [InternalDriverPieceWork] a
        LEFT JOIN [InternalDriverPieceWork] b ON b.InternalDriverName = a.InternalDriverName
                                                 AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                                       OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                                                     )
WHERE   a.WorkshiftDate = '2017-07-03'
        AND a.InternalDriverName = '陈尔鸿'
GROUP BY a.InternalDriverName

合成一条数据的显示还是要显示所有字段，不能单单只显示SIZE_20_VOLUME，UNIT_TOTAL_VOLUME，workshiftcode。还需要其他字段都显示出来。

二月十六版主 2017-07-28

打赏
举报

楼主我用的就是你给的那个数据，试着没问题；

SELECT * FROM [InternalDriverPieceWork] WHERE WorkshiftDate='2017-07-03' AND InternalDriverName='陈尔鸿'



SELECT  a.InternalDriverName ,

        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,

        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,

        MAX(CASE WHEN ISNULL(a.[SIZE_20_VOLUME], 0) = ISNULL(b.[SIZE_20_VOLUME],

                                                             0)

                 THEN ( CASE WHEN ISNULL(a.[UNIT_TOTAL_VOLUME], 0) > ISNULL(b.[UNIT_TOTAL_VOLUME],

                                                              0)

                             THEN a.[workshiftcode]

                             ELSE ISNULL(b.[workshiftcode], a.[workshiftcode])

                        END )

                 WHEN ISNULL(a.[SIZE_20_VOLUME], 0) > ISNULL(b.[SIZE_20_VOLUME],

                                                             0)

                 THEN a.[workshiftcode]

                 ELSE b.[workshiftcode]

            END) AS [workshiftcode]

FROM    [InternalDriverPieceWork] a

        LEFT JOIN [InternalDriverPieceWork] b ON b.InternalDriverName = a.InternalDriverName

                                                 AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME

                                                       OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME

                                                     )

WHERE   a.WorkshiftDate = '2017-07-03'

        AND a.InternalDriverName = '陈尔鸿'

GROUP BY a.InternalDriverName

RINK_1 2017-07-28

打赏
举报

引用 9 楼 u010133107 的回复:

USE [HyperCubeDW] GO /****** Object: Table [Operation].[InternalDriverPieceWork] Script Date: 2017/7/27 9:57:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Operation].[InternalDriverPieceWork]( [PieceWorkID] [int] IDENTITY(1,1) NOT NULL, [InternalDriverID] [nvarchar](10) NULL, [InternalDriverName] [nvarchar](20) NULL, [WeiXinID] [nvarchar](50) NULL, [WorkshiftDate] [date] NULL, [WorkshiftCode] [nvarchar](10) NULL, [Size_20_Volume] [int] NULL, [Size_40_Volume] [int] NULL, [Size_45_Volume] [int] NULL, [SP_Cntr_Volume] [int] NULL, [Hatch_Quantity] [int] NULL, [UNIT_Total_Volume] [int] NULL, CONSTRAINT [PK_InternalDriver_PieceWorkID] PRIMARY KEY CLUSTERED ( [PieceWorkID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [Operation].[InternalDriverPieceWork] ON INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14517, N'8009', N'陈绍伟', N'chenshaowei', CAST(N'2017-07-03' AS Date), N'NS', 92, 127, 0, 9, 0, 228) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14518, N'8240', N'伍梓鹏', N'wuzipeng', CAST(N'2017-07-03' AS Date), N'DS', 295, 40, 0, 0, 0, 335) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14519, N'8185', N'张文浩', N'zhangwenhao', CAST(N'2017-07-03' AS Date), N'NS', 134, 48, 0, 0, 0, 182) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14520, N'8124', N'邹国斌', N'zouguobin', CAST(N'2017-07-03' AS Date), N'DS', 224, 85, 0, 1, 0, 310) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14521, N'8029', N'张子龙', N'zhangzilong', CAST(N'2017-07-03' AS Date), N'DS', 142, 144, 0, 0, 0, 286) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14522, N'8096', N'郭程森', N'guochengsen', CAST(N'2017-07-03' AS Date), N'NS', 120, 53, 0, 8, 0, 181) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14523, N'8148', N'何桢', N'hezhen', CAST(N'2017-07-03' AS Date), N'DS', 308, 32, 0, 0, 0, 340) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14524, N'8150', N'罗志刚', N'luozhigang', CAST(N'2017-07-03' AS Date), N'NS', 290, 0, 0, 0, 0, 290) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14525, N'8215', N'黎乐彬', N'lilebin', CAST(N'2017-07-03' AS Date), N'NS', 126, 22, 0, 0, 0, 148) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14526, N'8058', N'郑继耿', N'zhengjigeng', CAST(N'2017-07-03' AS Date), N'NS', 105, 18, 0, 0, 0, 123) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14527, N'8219', N'林子杰', N'linzijie', CAST(N'2017-07-03' AS Date), N'NS', 186, 36, 0, 0, 0, 222) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14528, N'8126', N'陈智聪', N'chenzhicong', CAST(N'2017-07-03' AS Date), N'NS', 224, 64, 0, 8, 0, 296) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14529, N'8021', N'吴少俊', N'wushaojun', CAST(N'2017-07-03' AS Date), N'NS', 163, 94, 0, 0, 0, 257) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14530, N'8078', N'符华富', N'fuhuafu', CAST(N'2017-07-03' AS Date), N'DS', 195, 19, 0, 0, 0, 214) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14531, N'8101', N'朱慧安', N'zhuhuian', CAST(N'2017-07-03' AS Date), N'NS', 193, 45, 0, 0, 0, 238) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14532, N'8129', N'蔡钦滨', N'caiqinbin', CAST(N'2017-07-03' AS Date), N'DS', 171, 20, 0, 0, 0, 191) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14541, N'8129', N'蔡钦滨', N'caiqinbin', CAST(N'2017-07-03' AS Date), N'NS', 6, 0, 0, 0, 0, 6) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14542, N'8049', N'罗铭坚', N'luomingjian', CAST(N'2017-07-03' AS Date), N'DS', 90, 20, 0, 0, 0, 110) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14547, N'8320', N'谭炜坤', N'tanweikun', CAST(N'2017-07-03' AS Date), N'NS', 29, 31, 0, 3, 0, 63) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14548, N'8274', N'李繁鑫', NULL, CAST(N'2017-07-03' AS Date), N'NS', 86, 10, 0, 0, 0, 96) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14636, N'8255', N'陈尔鸿', N'chenerhong', CAST(N'2017-07-03' AS Date), N'NS', 6, 0, 0, 0, 0, 6) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14666, N'8255', N'陈尔鸿', N'chenerhong', CAST(N'2017-07-03' AS Date), N'DS', 39, 10, 0, 0, 0, 49) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14667, N'8313', N'陈伟斌', N'chenweibin', CAST(N'2017-07-03' AS Date), N'DS', 12, 0, 0, 0, 0, 12) GO SET IDENTITY_INSERT [Operation].[InternalDriverPieceWork] OFF 这是一天的数据的几个例子；里面有些人名有重复，需要根据使用两个条件判断，一个是SIZE_20_VOLUME, 一个是UNIT_TOTAL_VOLUME；当一个条件有相等的值，则用另个条件判断。判断值的大小，取大值的workshiftcode中的DS或者NS；然后合成一条数据。貌似二楼的语句比较符合，但是少了人员。

u010133107 2017-07-27

打赏
举报

USE [HyperCubeDW] GO /****** Object: Table [Operation].[InternalDriverPieceWork] Script Date: 2017/7/27 9:57:33 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [Operation].[InternalDriverPieceWork]( [PieceWorkID] [int] IDENTITY(1,1) NOT NULL, [InternalDriverID] [nvarchar](10) NULL, [InternalDriverName] [nvarchar](20) NULL, [WeiXinID] [nvarchar](50) NULL, [WorkshiftDate] [date] NULL, [WorkshiftCode] [nvarchar](10) NULL, [Size_20_Volume] [int] NULL, [Size_40_Volume] [int] NULL, [Size_45_Volume] [int] NULL, [SP_Cntr_Volume] [int] NULL, [Hatch_Quantity] [int] NULL, [UNIT_Total_Volume] [int] NULL, CONSTRAINT [PK_InternalDriver_PieceWorkID] PRIMARY KEY CLUSTERED ( [PieceWorkID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [Operation].[InternalDriverPieceWork] ON INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14517, N'8009', N'陈绍伟', N'chenshaowei', CAST(N'2017-07-03' AS Date), N'NS', 92, 127, 0, 9, 0, 228) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14518, N'8240', N'伍梓鹏', N'wuzipeng', CAST(N'2017-07-03' AS Date), N'DS', 295, 40, 0, 0, 0, 335) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14519, N'8185', N'张文浩', N'zhangwenhao', CAST(N'2017-07-03' AS Date), N'NS', 134, 48, 0, 0, 0, 182) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14520, N'8124', N'邹国斌', N'zouguobin', CAST(N'2017-07-03' AS Date), N'DS', 224, 85, 0, 1, 0, 310) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14521, N'8029', N'张子龙', N'zhangzilong', CAST(N'2017-07-03' AS Date), N'DS', 142, 144, 0, 0, 0, 286) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14522, N'8096', N'郭程森', N'guochengsen', CAST(N'2017-07-03' AS Date), N'NS', 120, 53, 0, 8, 0, 181) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14523, N'8148', N'何桢', N'hezhen', CAST(N'2017-07-03' AS Date), N'DS', 308, 32, 0, 0, 0, 340) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14524, N'8150', N'罗志刚', N'luozhigang', CAST(N'2017-07-03' AS Date), N'NS', 290, 0, 0, 0, 0, 290) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14525, N'8215', N'黎乐彬', N'lilebin', CAST(N'2017-07-03' AS Date), N'NS', 126, 22, 0, 0, 0, 148) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14526, N'8058', N'郑继耿', N'zhengjigeng', CAST(N'2017-07-03' AS Date), N'NS', 105, 18, 0, 0, 0, 123) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14527, N'8219', N'林子杰', N'linzijie', CAST(N'2017-07-03' AS Date), N'NS', 186, 36, 0, 0, 0, 222) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14528, N'8126', N'陈智聪', N'chenzhicong', CAST(N'2017-07-03' AS Date), N'NS', 224, 64, 0, 8, 0, 296) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14529, N'8021', N'吴少俊', N'wushaojun', CAST(N'2017-07-03' AS Date), N'NS', 163, 94, 0, 0, 0, 257) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14530, N'8078', N'符华富', N'fuhuafu', CAST(N'2017-07-03' AS Date), N'DS', 195, 19, 0, 0, 0, 214) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14531, N'8101', N'朱慧安', N'zhuhuian', CAST(N'2017-07-03' AS Date), N'NS', 193, 45, 0, 0, 0, 238) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14532, N'8129', N'蔡钦滨', N'caiqinbin', CAST(N'2017-07-03' AS Date), N'DS', 171, 20, 0, 0, 0, 191) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14541, N'8129', N'蔡钦滨', N'caiqinbin', CAST(N'2017-07-03' AS Date), N'NS', 6, 0, 0, 0, 0, 6) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14542, N'8049', N'罗铭坚', N'luomingjian', CAST(N'2017-07-03' AS Date), N'DS', 90, 20, 0, 0, 0, 110) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14547, N'8320', N'谭炜坤', N'tanweikun', CAST(N'2017-07-03' AS Date), N'NS', 29, 31, 0, 3, 0, 63) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14548, N'8274', N'李繁鑫', NULL, CAST(N'2017-07-03' AS Date), N'NS', 86, 10, 0, 0, 0, 96) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14636, N'8255', N'陈尔鸿', N'chenerhong', CAST(N'2017-07-03' AS Date), N'NS', 6, 0, 0, 0, 0, 6) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14666, N'8255', N'陈尔鸿', N'chenerhong', CAST(N'2017-07-03' AS Date), N'DS', 39, 10, 0, 0, 0, 49) INSERT [Operation].[InternalDriverPieceWork] ([PieceWorkID], [InternalDriverID], [InternalDriverName], [WeiXinID], [WorkshiftDate], [WorkshiftCode], [Size_20_Volume], [Size_40_Volume], [Size_45_Volume], [SP_Cntr_Volume], [Hatch_Quantity], [UNIT_Total_Volume]) VALUES (14667, N'8313', N'陈伟斌', N'chenweibin', CAST(N'2017-07-03' AS Date), N'DS', 12, 0, 0, 0, 0, 12) GO SET IDENTITY_INSERT [Operation].[InternalDriverPieceWork] OFF 这是一天的数据的几个例子；里面有些人名有重复，需要根据使用两个条件判断，一个是SIZE_20_VOLUME, 一个是UNIT_TOTAL_VOLUME；当一个条件有相等的值，则用另个条件判断。判断值的大小，取大值的workshiftcode中的DS或者NS；然后合成一条数据。貌似二楼的语句比较符合，但是少了人员。

u010133107 2017-07-27

打赏
举报

u010133107 2017-07-27

打赏
举报

引用 2 楼 RINK_1 的回复:

SELECT A.INTERNALDRIVERID, A.INTERNALDRIVERNAME, A.WORKSHIFTDATE, A.WEIXINID, A.size_20_volume+B.size_20_volume AS size_20_volume, A.Size_40_Volume+B.Size_40_Volume AS Size_40_Volume, A.Size_45_Volume+B.Size_45_Volume AS Size_45_Volume, A.SP_Cntr_Volume+B.SP_Cntr_Volume AS SP_Cntr_Volume, A.Hatch_Quantity+B.Hatch_Quantity AS Hatch_Quantity, A.UNIT_Total_Volume+B.UNIT_Total_Volume AS UNIT_Total_Volume CASE WHEN A.SIZE_20_VOLUME=B.SIZE_20_VOLUME THEN CASE WHEN A.UNIT_TOTAL_VOLUME>B.UNIT_TOTAL_VOLUME THEN A.workshiftcode WHEN A.UNIT_TOTAL_VOLUME<B.UNIT_TOTAL_VOLUME THEN B.workshiftcode ELSE '' END ELSE CASE WHEN A.SIZE_20_VOLUME>B.SIZE_20_VOLUME THEN A.workshiftcode WHEN A.SIZE_20_VOLUME<B.SIZE_20_VOLUME THEN B.workshiftcode ELSE '' END END AS workshiftcode_MAX FROM TABLE A JOIN TABLE B ON A.INTERNALDRIVERID=B.INTERNALDRIVERID AND A.WORKSHIFTDATE=B.WORKSHIFTDATE WHERE A.WORKSHIFTCODE='DS' AND B.WORKSHIFTCODE='NS'

2楼的比较接近，但是还是少了人员。如图：

二月十六版主 2017-07-27

打赏
举报

引用 5 楼 u010133107 的回复:

[quote=引用 1 楼 sinat_28984567 的回复:]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] int)
Insert #T
select 1,10,20,null union all
select 1,20,20,null union all
select 2,20,30,null union all
select 2,20,20,null union all
select 3,20,20,null
Go
--测试数据结束
SELECT  a.name ,
        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,
        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,
        MAX(CASE WHEN a.[SIZE_20_VOLUME] = ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN ( CASE WHEN a.[UNIT_TOTAL_VOLUME] > ISNULL(b.[UNIT_TOTAL_VOLUME],0)
                             THEN a.[UNIT_TOTAL_VOLUME]
                             ELSE b.[UNIT_TOTAL_VOLUME]
                        END )
                 WHEN a.[SIZE_20_VOLUME] > ISNULL(b.[SIZE_20_VOLUME],0)
                 THEN a.[SIZE_20_VOLUME]
                 ELSE b.[SIZE_20_VOLUME]
            END) AS [workshiftcode]
FROM    #T a
        LEFT JOIN #T b ON b.name = a.name
                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME
                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME
                              )
GROUP BY a.name

1楼的语句查询出来也是一样，值不对。某一天的作业量没有那么多。且需要得到直达的workshiftcode 的代码，不是值。

[/quote] 其实我没看明白是什么需求……如果可以建议楼主给出表结构、测试数据、规则、以及基于测试数据的正确结果

u010133107 2017-07-27

打赏
举报

引用 1 楼 sinat_28984567 的回复:

--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

Create table #T([name] int,[SIZE_20_VOLUME] int,[UNIT_TOTAL_VOLUME] int,[workshiftcode] int)

Insert #T

select 1,10,20,null union all

select 1,20,20,null union all

select 2,20,30,null union all

select 2,20,20,null union all

select 3,20,20,null

Go

--测试数据结束

SELECT  a.name ,

        SUM(a.SIZE_20_VOLUME) AS SIZE_20_VOLUME ,

        SUM(a.[UNIT_TOTAL_VOLUME]) AS [UNIT_TOTAL_VOLUME] ,

        MAX(CASE WHEN a.[SIZE_20_VOLUME] = ISNULL(b.[SIZE_20_VOLUME],0)

                 THEN ( CASE WHEN a.[UNIT_TOTAL_VOLUME] > ISNULL(b.[UNIT_TOTAL_VOLUME],0)

                             THEN a.[UNIT_TOTAL_VOLUME]

                             ELSE b.[UNIT_TOTAL_VOLUME]

                        END )

                 WHEN a.[SIZE_20_VOLUME] > ISNULL(b.[SIZE_20_VOLUME],0)

                 THEN a.[SIZE_20_VOLUME]

                 ELSE b.[SIZE_20_VOLUME]

            END) AS [workshiftcode]

FROM    #T a

        LEFT JOIN #T b ON b.name = a.name

                          AND ( b.SIZE_20_VOLUME <> a.SIZE_20_VOLUME

                                OR b.UNIT_TOTAL_VOLUME <> a.UNIT_TOTAL_VOLUME

                              )

GROUP BY a.name

1楼的语句查询出来也是一样，值不对。某一天的作业量没有那么多。且需要得到直达的workshiftcode 的代码，不是值。

u010133107 2017-07-27

打赏
举报

引用 3 楼 ch21st 的回复:



if not object_id(N'Tempdb..#t') is null drop table #t



Create table #t(drivername nvarchar(10),driverid varchar(20),workshiftcode varchar(2),size_20_volume int

                ,Size_40_Volume int,Size_45_Volume int,SP_Cntr_Volume int,Hatch_Quantity int,UNIT_Total_Volume int)



Insert into #t

select N'陈火','chenhuo','NS',0,5,0,0,0,4 union all

select N'陈火城','chenhuocheng','NS',0,4,0,0,0,4 union all

select N'陈火城','chenhuocheng','DS',0,3,0,0,0,9

select * from (

    

   select drivername,driverid,workshiftcode

        ,sum(size_20_volume)over(partition by drivername,driverid) as size_20_volume

        ,sum(Size_40_Volume)over(partition by drivername,driverid) as Size_40_Volume

        ,sum(Size_45_Volume)over(partition by drivername,driverid) as Size_45_Volume

        ,sum(SP_Cntr_Volume)over(partition by drivername,driverid) as SP_Cntr_Volume

        ,sum(Hatch_Quantity)over(partition by drivername,driverid) as Hatch_Quantity

        ,sum(UNIT_Total_Volume)over(partition by drivername,driverid) as UNIT_Total_Volume

  ,row_number()over(partition by drivername,driverid order by size_20_volume desc,UNIT_Total_Volume desc) as rn from #t

) as t where t.rn=1



  	drivername	driverid	workshiftcode	size_20_volume	Size_40_Volume	Size_45_Volume	SP_Cntr_Volume	Hatch_Quantity	UNIT_Total_Volume

1	陈火	chenhuo	NS	0	5	0	0	0	4

2	陈火城	chenhuocheng	NS	0	4	0	0	0	4

3	陈火城	chenhuocheng	DS	0	3	0	0	0	9

结果

  	drivername	driverid	workshiftcode	size_20_volume	Size_40_Volume	Size_45_Volume	SP_Cntr_Volume	Hatch_Quantity	UNIT_Total_Volume	rn

1	陈火	chenhuo	NS	0	5	0	0	0	4	1

2	陈火城	chenhuocheng	DS	0	7	0	0	0	13	1

试了这个，在where 后面加上了具体的日期，查询结果发现数据量减少了，3号不止8个人又作业。且值也不对。