首页 新闻 搜索 专区 学院

一个T-sql语句

1
[待解决问题]
<table border="0" cellspacing="0" cellpadding="0" width="144"> <colgroup span="1"><col span="2" width="72"></col></colgroup> <tbody> <tr height="18"> <td colspan="2" width="144" height="18">TableA&nbsp;</td> </tr> <tr height="18"> <td height="18">type</td> <td>value</td> </tr> <tr height="18"> <td height="18">A</td> <td>A1</td> </tr> <tr height="18"> <td height="18">A</td> <td>A2</td> </tr> <tr height="18"> <td height="18">A</td> <td>A3</td> </tr> <tr height="18"> <td height="18">A</td> <td>A4</td> </tr> <tr height="18"> <td height="18">B</td> <td>B1</td> </tr> <tr height="18"> <td height="18">B</td> <td>B3</td> </tr> <tr height="18"> <td height="18">B</td> <td>B4</td> </tr> <tr height="18"> <td colspan="2" height="18">TableB</td> </tr> <tr height="18"> <td height="18">Type</td> <td>Counts</td> </tr> <tr height="18"> <td height="18">A</td> <td align="right">2</td> </tr> <tr height="18"> <td height="18">B</td> <td align="right">3</td> </tr> <tr height="18"> <td colspan="2" height="18">想要结果:</td> </tr> <tr height="18"> <td height="18">A</td> <td>A1</td> </tr> <tr height="18"> <td height="18">A</td> <td>A2</td> </tr> <tr height="18"> <td height="18">B</td> <td>B1</td> </tr> <tr height="18"> <td height="18">B</td> <td>B3</td> </tr> <tr height="18"> <td height="18">B</td> <td>B4</td> </tr> </tbody> </table>
问题补充: 已经得到如下结果集 TableK Type value orderid(序号) counts B apple 1 3 B oranage 2 4 B pear 3 4 A blank 4 4 A white 5 4 A pink 6 4 A red 7 4 A blue 8 4 A oranage 9 4 想要在tablek中取出3条B类型,4条A类型
cuxin的主页 cuxin | 菜鸟二级 | 园豆:285
提问于:2010-09-30 12:55
< >
分享
所有回答(5)
0

declare @t table(type varchar(10),value varchar(10))

insert @t
select 'A', 'A1'
union all
select 'A', 'A2'
union all
select 'A', 'A3'
union all
select 'B', 'B1'
union all
select 'B', 'B2'
union all
select 'B', 'B3'

declare @t1 table(type varchar(10),count int)

insert @t1
select 'A', 2
union all
select 'B', 3


select * from
(
select a.*,b.count from @t a left join @t1 b on a.type=b.type
)a
where right(value,1)<=count

 

清海扬波 | 园豆:756 (小虾三级) | 2010-09-30 14:22
感谢你的回答,但是value那列的值是不规则的
支持(0) 反对(0) cuxin | 园豆:285 (菜鸟二级) | 2010-09-30 15:54
如果是2005+,那么就像2楼那样就可以了,不然自己弄一个分组序列号就可以了,关键是把这个数字取出来。
支持(0) 反对(0) 清海扬波 | 园豆:756 (小虾三级) | 2010-09-30 15:58
0

select [Type],[Value] from

(

SELECT TableA.Type,TableA.Value,TableB.Counts,

row_number() over(PARTITION  BY TableA.Type ORDER BY TableA.Type) AS [序号]  

FROM  TableA inner join TableB ON TableB.Type= TableA.Type

) b where b.[序号]<=b.Counts

fjcoder | 园豆:245 (菜鸟二级) | 2010-09-30 15:28
已经得到如下结果集 TableK Type value orderid(序号) counts B apple 1 3 B oranage 2 4 B pear 3 4 A blank 4 4 A white 5 4 A pink 6 4 A red 7 4 A blue 8 4 A oranage 9 4 想要在tablek中取出3条B类型,4条A类型
支持(0) 反对(0) cuxin | 园豆:285 (菜鸟二级) | 2010-09-30 16:29
@cuxin: select [Type],[Value],OrderId,Counts from ( select TableK.*, row_number() over (partition by [Type] order by [Type]) as groupid, temp=(select min(b.counts) from TableK b where b.Type = TableK.type) from TableK ) tb where tb.groupid<=temp order by tb.orderid
支持(0) 反对(0) fjcoder | 园豆:245 (菜鸟二级) | 2010-10-01 12:22
0

可以在存储过程中实现。

-----------------创建测试数据表-----------------

CREATE TABLE [t] (
  type varchar(10),value varchar(10)
)
 
insert t
select 'A',     'a'
union all select 'A',     'aa'
union all select 'A',     'aaa'
union all select 'B',     'b'
union all select 'B',     'bb'
union all select 'B',     'bbb'

CREATE TABLE [t1] (
 type varchar(10),counts int
)

insert t1
select 'A',     2
union all select 'B',     3
----------------------------------------

---------以下是存储过程中的语句块儿----------

----创建临时表,用于存放结果
CREATE TABLE [#T_All] (
  type varchar(10),value varchar(10)
)

declare @sql varchar(2000)
declare @type varchar(255),@counts int
 ----使用游标遍历,将查询结果集存储到临时表--
declare  MyCursor cursor  for
select type,counts from t1
 
open  MyCursor
FETCH NEXT FROM MyCursor into @type,@counts
while @@fetch_status = 0
begin
 
set @sql = 'insert into #T_All(type,value) '
 +' select top '+cast(@counts as varchar(10))+' type,value from t '
 +' where type = '''+@type+''''
exec (@sql)

FETCH NEXT FROM MyCursor  into @type,@counts
end
CLOSE MyCursor
DEALLOCATE MyCursor

--------得到结果------

select * from #T_All

--------删除测试表------,别把你自己的表删了哟 :)

drop table t
drop table t1
drop table #T_All

冰是睡着的水 | 园豆:110 (初学一级) | 2010-09-30 16:44
0

--不知道
with TableA([type],value) as (
select 'A', 'A1'
union all
select 'A', 'A2'
union all
select 'A', 'A3'
union all
select 'B', 'B1'
union all
select 'B', 'B2'
union all
select 'B', 'B3'
),
TableB(
[type],counts) as (
select 'A', 2
union all
select 'B', 3
)
select a.[type],a.value from
(
select [type],value,
row_number()
over(partition by type order by value) rn
from TableA
) a, TableB b
where a.rn <= b.counts
and a.[type] = b.[type]

type value
---- -----
A A1
A A2
B B1
B B2
B B3

 

killkill | 园豆:992 (小虾三级) | 2010-09-30 20:54
这可以
支持(0) 反对(0) liheping | 园豆:190 (初学一级) | 2010-10-01 21:13
0

killkill写的不错 学习了

SELECT A.[type],A.[value] FROM (
SELECT [type],[value],ROW_NUMBER() OVER(PARTITION BY [type] ORDER BY [value]) RN 
 FROM TableA
)A, TableB B
WHERE A.RN<=B.[Counts] AND A.[type]=B.[type]

 


liheping | 园豆:190 (初学一级) | 2010-10-01 21:14
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册