with cte as ( select *,rid = row_number() over (partition by dw order by getdate()) from tb )select qty,dw from cte where rid = 1
select QTY=(select top 1 QTY from tb where DW=a.DW), DW from tb group by DW
select QTY=(select top 1 QTY from tb where DW=a.DW), DW from tb a --外表别名 group by DW
if object_id('[TB]') is not null drop table [TB] go create table [TB] (QTY int,DW nvarchar(2)) insert into [TB] select 1,'双' union all select 2,'双' union all select 3,'只' union all select 4,'只' union all select 5,'件' union all select 6,'件' union all select 6,'件' union all select 7,'条' union all select 8,'箱'select * from [TB] SELECT MIN(qty) AS qty,dw FROM dbo.TB GROUP BY dw ORDER BY qty /* qty dw 1 双 3 只 5 件 7 条 8 箱*/??
declare @a table(QTY INT,DW VARCHAR(20)) insert into @a select 1 ,'双' union all select 2 ,'双' union all select 3 ,'只' union all select 4 ,'只' union all select 5 ,'件' union all select 6 ,'件' union all select 6 ,'件' union all select 7 ,'条' union all select 8 ,'箱'
select DISTINCT QTY,DW from @a a where not exists(select 1 from @a where DW = a.DW and QTY < a.QTY)
select QTY=(select top 1 QTY from tb where DW=a.DW), DW from tb group by DW
with cte as
(
select *,rid = row_number() over (partition by dw order by getdate())
from tb
)select qty,dw
from cte
where rid = 1
DW
from tb group by DW
select QTY=(select top 1 QTY from tb where DW=a.DW),
DW
from tb a --外表别名
group by DW
go
create table [TB] (QTY int,DW nvarchar(2))
insert into [TB]
select 1,'双' union all
select 2,'双' union all
select 3,'只' union all
select 4,'只' union all
select 5,'件' union all
select 6,'件' union all
select 6,'件' union all
select 7,'条' union all
select 8,'箱'select * from [TB]
SELECT MIN(qty) AS qty,dw
FROM dbo.TB
GROUP BY dw
ORDER BY qty
/*
qty dw
1 双
3 只
5 件
7 条
8 箱*/??
大哥。这个好像不对吧。报错了。
'row_number' 不是可以识别的 函数名。
在关键字 'with' 附近有语法错误。
insert into @a
select 1 ,'双' union all
select 2 ,'双' union all
select 3 ,'只' union all
select 4 ,'只' union all
select 5 ,'件' union all
select 6 ,'件' union all
select 6 ,'件' union all
select 7 ,'条' union all
select 8 ,'箱'
select DISTINCT QTY,DW from @a a where not exists(select 1 from @a where DW = a.DW and QTY < a.QTY)
QTY=(select top 1 QTY from tb where DW=a.DW),
DW
from
tb
group by
DW