sqlserversql语句问题

下面sql 是where下的，想实现 where STWEK = Z1WEK + 2

STWEK，Z1WEK都是 202249这种格式的数据，表示2022年第49周

因为怕Z1WEK + 2周数会大于一年的周数，涉及到年会加1，所以改成下面这这样，但是好像有问题，Z1WEK写死成数据202249就好用，但是不写死就查不到数据，有大佬知道怎么回事吗

where STWEK = (

select

TOP 1

case when SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2) <= DATEPART(wk,CONCAT(left(Z1WEK,4),'-12','-31')) then CONVERT(int,Z1WEK) + 2

when SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),'-12','-31')) < 10 then CONVERT(char(4),CONVERT(int,SUBSTRING(Z1WEK,1,4)) + 1) + '0' +CONVERT(char(2),(SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),'-12','-31'))))

when SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),'-12','-31')) > 9 then CONVERT(char(4),CONVERT(int,SUBSTRING(Z1WEK,1,4)) + 1)+CONVERT(char(2),SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),'-12','-31')))

end

from dbo.ZNMS_JIT_H1_H)

方式二：

FineReport SQL 狂奔的小蜗牛 发布于 2022-12-29 10:26 （编辑于 2022-12-29 16:24）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

yzm339714Lv6中级互助
发布于2022-12-29 10:28

where STWEK in (

select

TOP 1

case when SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2) <= DATEPART(wk,CONCAT(left(Z1WEK,4),'-12','-31')) then CONVERT(int,Z1WEK) + 2

end as STWEK

from dbo.ZNMS_JIT_H1_H)

举报收起评论(12)

狂奔的小蜗牛(提问者) 大佬，还是没数据啊，还是没查到

2022-12-29 10:33

yzm339714 回复狂奔的小蜗牛(提问者) 那你中间的放数据库执行一下看看结果是啥，太复杂了

2022-12-29 10:37

狂奔的小蜗牛(提问者) 回复 yzm339714 是你写的单独执行一下吗，我试了一下，select * from 表 where 你写的sql 还是没数据

2022-12-29 10:58

yzm339714 回复狂奔的小蜗牛(提问者) select TOP 1 case when SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2) <= DATEPART(wk,CONCAT(left(Z1WEK,4),\'-12\',\'-31\')) then CONVERT(int,Z1WEK) + 2 when SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),\'-12\',\'-31\')) < 10 then CONVERT(char(4),CONVERT(int,SUBSTRING(Z1WEK,1,4)) + 1) + \'0\' +CONVERT(char(2),(SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),\'-12\',\'-31\')))) when SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),\'-12\',\'-31\')) > 9 then CONVERT(char(4),CONVERT(int,SUBSTRING(Z1WEK,1,4)) + 1)+CONVERT(char(2),SUBSTRING(CONVERT(char(10),CONVERT(int,Z1WEK) + 2), 5, 2)-DATEPART(wk,CONCAT(left(Z1WEK,4),\'-12\',\'-31\'))) end as STWEK from dbo.ZNMS_JIT_H1_H 你执行下这个，中间取值的sql,看看有没有结果

2022-12-29 11:08

狂奔的小蜗牛(提问者) 回复 yzm339714 还是没值啊。。。

2022-12-29 13:02

还有7条评论点击查看

Z4u3z1Lv6专家互助
发布于2022-12-29 10:38（编辑于 2022-12-29 14:32）

WITH TEST AS (

SELECT '202201' [STWEK],'202201' [Z1WEK] UNION ALL

SELECT '202202' [STWEK],'202202' [Z1WEK] UNION ALL

SELECT '202203' [STWEK],'202203' [Z1WEK] UNION ALL

SELECT '202204' [STWEK],'202204' [Z1WEK] UNION ALL

SELECT '202205' [STWEK],'202205' [Z1WEK] UNION ALL

SELECT '202206' [STWEK],'202206' [Z1WEK] UNION ALL

SELECT '202207' [STWEK],'202207' [Z1WEK] UNION ALL

SELECT '202208' [STWEK],'202208' [Z1WEK] UNION ALL

SELECT '202209' [STWEK],'202209' [Z1WEK] UNION ALL

SELECT '202210' [STWEK],'202210' [Z1WEK] UNION ALL

SELECT '202211' [STWEK],'202211' [Z1WEK] UNION ALL

SELECT '202212' [STWEK],'202212' [Z1WEK] UNION ALL

SELECT '202213' [STWEK],'202213' [Z1WEK] UNION ALL

SELECT '202214' [STWEK],'202214' [Z1WEK] UNION ALL

SELECT '202215' [STWEK],'202215' [Z1WEK] UNION ALL

SELECT '202216' [STWEK],'202216' [Z1WEK] UNION ALL

SELECT '202217' [STWEK],'202217' [Z1WEK] UNION ALL

SELECT '202218' [STWEK],'202218' [Z1WEK] UNION ALL

SELECT '202219' [STWEK],'202219' [Z1WEK] UNION ALL

SELECT '202220' [STWEK],'202220' [Z1WEK] UNION ALL

SELECT '202221' [STWEK],'202221' [Z1WEK] UNION ALL

SELECT '202222' [STWEK],'202222' [Z1WEK] UNION ALL

SELECT '202223' [STWEK],'202223' [Z1WEK] UNION ALL

SELECT '202224' [STWEK],'202224' [Z1WEK] UNION ALL

SELECT '202225' [STWEK],'202225' [Z1WEK] UNION ALL

SELECT '202226' [STWEK],'202226' [Z1WEK] UNION ALL

SELECT '202227' [STWEK],'202227' [Z1WEK] UNION ALL

SELECT '202228' [STWEK],'202228' [Z1WEK] UNION ALL

SELECT '202229' [STWEK],'202229' [Z1WEK] UNION ALL

SELECT '202230' [STWEK],'202230' [Z1WEK] UNION ALL

SELECT '202231' [STWEK],'202231' [Z1WEK] UNION ALL

SELECT '202232' [STWEK],'202232' [Z1WEK] UNION ALL

SELECT '202233' [STWEK],'202233' [Z1WEK] UNION ALL

SELECT '202234' [STWEK],'202234' [Z1WEK] UNION ALL

SELECT '202235' [STWEK],'202235' [Z1WEK] UNION ALL

SELECT '202236' [STWEK],'202236' [Z1WEK] UNION ALL

SELECT '202237' [STWEK],'202237' [Z1WEK] UNION ALL

SELECT '202238' [STWEK],'202238' [Z1WEK] UNION ALL

SELECT '202239' [STWEK],'202239' [Z1WEK] UNION ALL

SELECT '202240' [STWEK],'202240' [Z1WEK] UNION ALL

SELECT '202241' [STWEK],'202241' [Z1WEK] UNION ALL

SELECT '202242' [STWEK],'202242' [Z1WEK] UNION ALL

SELECT '202243' [STWEK],'202243' [Z1WEK] UNION ALL

SELECT '202244' [STWEK],'202244' [Z1WEK] UNION ALL

SELECT '202245' [STWEK],'202245' [Z1WEK] UNION ALL

SELECT '202246' [STWEK],'202246' [Z1WEK] UNION ALL

SELECT '202247' [STWEK],'202247' [Z1WEK] UNION ALL

SELECT '202248' [STWEK],'202248' [Z1WEK] UNION ALL

SELECT '202249' [STWEK],'202249' [Z1WEK] UNION ALL

SELECT '202250' [STWEK],'202250' [Z1WEK] UNION ALL

SELECT '202253' [STWEK],'202251' [Z1WEK] UNION ALL

SELECT '202301' [STWEK],'202252' [Z1WEK] UNION ALL

SELECT '202302' [STWEK],'202253' [Z1WEK]

)

SELECT * FROM TEST WHERE STWEK= (

case when (CONVERT(BIGINT,(right(Z1WEK,2)))+2)>DATEPART(WW,CONCAT(LEFT(Z1WEK,4),'-12-31'))

then CONCAT(CONVERT(BIGINT,(LEFT(Z1WEK,4)))+1,(CONVERT(BIGINT,(right(Z1WEK,2))))+2)-DATEPART(WW,CONCAT(LEFT(Z1WEK,4),'-12-31'))

ELSE CONVERT(BIGINT,(Z1WEK))+2 end )

举报收起评论(17)

狂奔的小蜗牛(提问者) 你写的这个有0拼接周和1拼接周的情况吗，写全啊大佬，用了我看不懂的方式写不全我不会改啊

2022-12-29 10:55

狂奔的小蜗牛(提问者) 我复制了一下，报错消息 102，级别 15，状态 1，第 21 行 “esle”附近有语法错误。

2022-12-29 10:56

Z4u3z1 回复狂奔的小蜗牛(提问者) ((right(Z1WEK,2)*1.0)+2)也就是获取Z1WEK 周数+2后的值，如果它大于了 DATEPART(WW,CONCAT(RIGHT(Z1WEK,4),\'-12-31\')) (这个是获取本年最后一天的周数也就是本年的周数)，就拼接(RIGHT(Z1WEK,4)*1.0)+1 （年份），0 （补位），((right(Z1WEK,2)*1.0)+2)-DATEPART(WW,CONCAT(RIGHT(Z1WEK,4),\'-12-31\')) （跨年后的周数）。如果Z1WEK 周数+2后的值小于等于本年周数就 (Z1WEK*1.0)+2 呗（*1.0是将字段直接转为浮点数）

2022-12-29 11:04