Select aa.*
From @a aa
Join
(Select name,min(number) as number from @a group by name ) bb
on aa.name = bb.name and aa.number = bb.number
结果当然是出来3个单位,如果你想出来一个单还得加条件(根据你的需要)。
如:
Select aa.*
From @a aa
Join
(Select name,min(number) as number from @a group by name ) bb
on aa.name = bb.name and aa.number = bb.number and aa.unit='只'
From @a aa
Join
(Select name,min(number) as number from @a group by name ) bb
on aa.name = bb.name and aa.number = bb.number
结果当然是出来3个单位,如果你想出来一个单还得加条件(根据你的需要)。
如:
Select aa.*
From @a aa
Join
(Select name,min(number) as number from @a group by name ) bb
on aa.name = bb.name and aa.number = bb.number and aa.unit='只'
解决方案 »
- 关于表建索引的问题
- 如何批量修改表结构?
- 求教SQL连接问题!
- SQLSERVER 倒入文本文件的问题,谢谢!
- dts导出数据的简单问题,解决即给分
- 关于AspNetPager的SQL2005储存过程转2000的问题
- 关于oracle的自定义类型:MDSYS.SDO_GEOMETRY,对应于SqlServer的什么类型
- 一个为难已久的SQL问题
- sqlserver 如何利用fn_dblog( )函数进行日志解析?
- 本地机器中sql数据库中的数据自动追加(或读取)到(本软件)另一台机器的access数据库里,这个问题我已经问过了但是没有很好的答案!!
- 这样的横纵项转换,如何实现!等……
- 怎樣簡單的轉化2004-01-06 11:36:26.907成20040106
insert @a select '201',1000,1.5,'只'
union select '201',100,1.5,'小件'
union select '201',0,1.5,'大件'
union select '301',1000,0.5,'只'
union select '301',10,0.5,'千克'Select aa.*
From @a aa
Join
(Select name,min(number) as number from @a group by name ) bb
on aa.name = bb.name and aa.number = bb.number/*
name number price unit
-------------------------------------------------- -------------------- -------------------- ----------
201 .0000 1.5000 大件
301 10.0000 .5000 千克(所影响的行数为 2 行)
*/
不是对的?
union select '201',100,1.5,'小件'
union select '201',10,1.5,'大件'
其实你输入的数据并不合理,当名字相同怎么能使单位不一样呢!
insert @a select '201',0,1.5,'只'
union select '201',0,1.5,'小件'
union select '201',0,1.5,'大件'
union select '301',1000,0.5,'只'
union select '301',10,0.5,'千克'201的数量=0时,仓库里有0存在的啊。=0时就不行了,<>=时是对的!!
insert @a select '201',0,1.5,'只'
union select '201',0,1.5,'小件'
union select '201',0,1.5,'大件'
union select '301',1000,0.5,'只'
union select '301',10,0.5,'千克'select * from @a aa where unit=(select top 1 unit from @a tem where number=(select min(number) from @a where name=tem.name) and number=aa.number and name=aa.name order by charindex(unit,'千克,只,小件,大件'))
insert @a select '原材料','201',0,1.5,'只'
union select '原材料','201',0,1.5,'小件'
union select '原材料','201',0,1.5,'大件'
union select '原材料','301',1000,0.5,'只'
union select '原材料','301',10,0.5,'千克'
--改成这样有什么漏洞吗????Select aa.*
From @a aa
Join
(Select GoodsName,min(number) as number from @a group by GoodsName ) bb
on aa.GoodsName = bb.GoodsName and aa.number = bb.number
where aa.Number > 0
union
select * from @a where Number = 0 and unit =(select Max(unit) from @a where number = 0)