组通过与使用条件

问题描述：

我在下面所示的下列数据：组通过与使用条件

stockid  stockname units typeid stocktype 
    321   stn433  gl  2   
    433   stn433  gl  2 
    432   stn433  gl  2   pick 
    544   stn664  gl  1   
    533   stn664  gl  1 
    655   stn555  gl  3   pick 
    677   stn555  gl  3

基于一个stockname，单位，typeid的组合（由stockname，单位，typeid的基团），如果存在的任何值'选择'stocktype，使用stockid或者选择组合的最小stockid。

我在下面显示了输出应该是什么样子。我知道我可以很容易地通过股票名称，单位，typeid进行分组，然后获得min（stockid），但是我需要先检查是否有任何'pick'的值，如果选择其他人选择min（sotckid）。我无法弄清楚如何做到这一点。先谢谢你。

注意：下面的输出是分组由股票名称，单位，typeid显示适当的stockid。

OUTPUT：

stockid  stockname units typeid 
    432   stn433  gl  2 
    533   stn664  gl  1 
    655   stn555  gl  3

我使用SQL Server 2016 - TSQL。

发生了什么股票'stn555 '？ –

答

你可以使用它。

DECLARE @SampleData TABLE(stockid INT, stockname VARCHAR(10), units VARCHAR(20), typeid INT, stocktype VARCHAR(20)) 

INSERT INTO @SampleData VALUES 
    (321   ,'stn433','gl',  2, NULL),   
    (433   ,'stn433','gl',  2, NULL), 
    (432   ,'stn433','gl',  2, 'pick'), 
    (544   ,'stn664','gl',  1, NULL),   
    (533   ,'stn664','gl',  1, NULL), 
    (655   ,'stn555','gl',  3, 'pick'), 
    (677   ,'stn555','gl',  3, NULL) 


;WITH PickTable AS (
    SELECT MIN(stockid) stockid, stockname, units, typeid FROM @SampleData 
    WHERE stocktype ='pick' 
    GROUP BY stockname, units, typeid 
) 
,NotPickTable 
AS (
    SELECT MIN(stockid) stockid, stockname, units, typeid FROM @SampleData T 
    WHERE NOT EXISTS(SELECT * FROM PickTable P WHERE P.stockname = T.stockname AND P.units= T.units AND P.typeid = T.typeid) 
    GROUP BY stockname, units, typeid 
) 
SELECT * FROM PickTable 
UNION 
SELECT * FROM NotPickTable

sarslan，我想使用SELECT * FROM PickTable UNION SELECT * FROM NotPickTable包含在CTE表中。那是怎么回事？ –

只需使用此查询替换上一个联合选择。，UniTable AS（ \t SELECT * FROM PickTable \t UNION \t SELECT * FROM NotPickTable ） SELECT * FROM UniTable –

答

我没有完全理解这个问题，但我会说你需要使用一个case语句

 CASE WHEN stocktype = 'pick' group by..... 
    ELSE min(sotckid) 
    END

您可能需要一个带分区的case语句，具体取决于您想要实现的内容 –

答

一种方法是使用row_number()：

select t.* 
from (select t.*, 
      row_number() over (partition by stockname, units, typeid 
           order by (case when stocktype = 'pick' then 1 else 2 end) 
           ) as seqnum 
     from t 
    ) t 
where seqnum = 1;

Gordon，抱歉，那是我的错误。看看原来的桌子。如果您有任何问题，请告诉我。 –

答

一个解决方案，而CASE

SELECT * FROM stack WHERE stocktype = 'pick' 
UNION ALL 
SELECT * FROM stack 
WHERE stockid IN 
(
    SELECT MIN(stockid) FROM stack 
    WHERE stockname NOT IN (
    SELECT stockname FROM stack WHERE stocktype = 'pick' 
    ) 
    GROUP BY stockname 
)

嗨Radim，我需要通过股票名称，单位，typeid进行分组，然后根据第一轮是否选择'pick'来显示该组的stockid，然后选择该组的stockid，否则选择min（stockid）来自团队。无论如何，你可以修改你的查询来做到这一点，并根据组合显示stockid，stockname，单位，typeid字段。 –

答

这是一个简单的查询，它得到期望的结果：

select case when IsPick is NULL then minStockId else StockId end AS stockid, 
     stockname, units, typeid 
from (
    select min(stocktype) AS IsPick, 
      max(case when stocktype is not null then stockid end) AS StockId, 
      min(stockid) AS minStockId, 
      stockname, 
      units, 
      typeid 
    from @x 
    group by stockname, units, typeid 
) AS A

答

这几乎是答案sarslan，但现在我将它张贴:)

with data as(
SELECT [Stockid] 
    ,[StockName] 
    ,[units] 
    ,[typeid] 
    ,[stocktype] 
    ,case when stocktype IS not null then 1 else 0 end as Flag 
FROM [LegOgSpass].[dbo].[stock] 
) 

,picks as (
select min([Stockid]) as StockID 
    ,[StockName] 
    ,[units] 
    ,[typeid] 
    from data 
    where Flag = 1 
    group by Stockname,units,typeid 
) 


,nopicks as (
select MIN(a.stockid) as Stockid,a.StockName,a.Units,a.typeid from data a 
where a.Flag= 0 and not exists (select typeid from picks b where a.typeid = 
b.typeid) 


group by a.stockname,a.units,a.typeid 
) 
Select * from picks 
union all 
select * from nopicks

组通过与使用条件

相关推荐