一个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 </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类型
所有回答(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
感谢你的回答，但是value那列的值是不规则的
如果是2005+，那么就像2楼那样就可以了，不然自己弄一个分组序列号就可以了，关键是把这个数字取出来。
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
已经得到如下结果集 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:
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
可以在存储过程中实现。
-----------------创建测试数据表-----------------
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
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
这可以
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]
