SQL添加条件查询

Question

在我的数据库的一个表中，我有12列，month_1 ...month_12。

我想在SQL查询中添加条件，如果@m =1就可以

CREATE PROCEDURE XemDiem_Top5Month1
    @m INT
AS
BEGIN
    SELECT *
    FROM XemDiem
    WHERE (month_1 IN (SELECT TOP (5) month_1
                       FROM XemDiem 
                       GROUP BY month_1
                       ORDER BY month_1DESC))
END

如果@m =2就可以了

CREATE PROCEDURE XemDiem_Top5Month1
    @m INT
AS
BEGIN
    SELECT *
    FROM XemDiem
    WHERE (month_2 IN (SELECT TOP (5) month_2
                       FROM XemDiem 
                       GROUP BY month_2
                       ORDER BY month_2 DESC))
END

以此类推...

我不想写太多的查询，所以请帮助

Answer 1

将select查询分配给一个字符串并连接参数。最后执行该字符串。

Create proc XemDiem_Top5Month1
@m int
AS
BEGIN
Delcare @query nvarchar(max) 
set @query='SELECT *
FROM XemDiem
WHERE 
(
  month_''+Convert(varchar(10),@m)+'' IN 
  (
    SELECT TOP (5) month_''+Convert(varchar(10),@m)+''
    FROM XemDiem 
    GROUP BY month_''+Convert(varchar(10),@m)+''
    ORDER BY month_''+Convert(varchar(10),@m)+'' DESC
  )
)'
execute(@query)

Answer 2

declare @ishistoric varchar(100) ='month_1'
(SELECT *
FROM XemDiem
WHERE 
(
 CASE @ishistoric
                  WHEN 'month_1' THEN month_1
                  WHEN 'month_2' THEN month_2
                  ...
                  WHEN 'month_12' THEN month_12
             END  IN 
(
SELECT TOP (5) CASE @ishistoric
                  WHEN 'month_1' THEN month_1
                  WHEN 'month_2' THEN month_2
                  ...
                  WHEN 'month_12' THEN month_12
             END
FROM OSUSR_KIA_PRINTSTICKER 
GROUP BY  CASE @ishistoric
                  WHEN 'month_1' THEN month_1
                  WHEN 'month_2' THEN month_2
                  ...
                  WHEN 'month_12' THEN month_12
             END
ORDER BY CASE @ishistoric
                  WHEN 'month_1' THEN month_1
                  WHEN 'month_2' THEN month_2
                  ...
                  WHEN 'month_12' THEN month_12
             END DESC
)
))

执行剩余的查询就像这样，查询基于提供的列&它将返回特定列的相应记录，这些记录被传递到参数中。