SQl查询重复行 [英] SQl query for repeat rows

问题描述

姓名泰卢固语图片价格quan



jaen123 700 bcb6a0b-.gif 805 23

tshirt35 350 9df27e4-.gif 402.5 35

jeans30-32 600 47d975c-.gif 690 50

jack1 800 4698ee8-.gif 920 25





i希望显示这个像





jack1 800 4698ee8-.gif 920

jack1 800 4698ee8-.gif 920

jack1 800 4698ee8-.gif 920

jack1 800 4698ee8-.gif 920

jack1 800 4698ee8-。 gif 920

jack1 800 4698ee8-.gif 920表示25次



表示根据数量

Name Telugu Image price quan

jaen123 ౭౦౦ bcb6a0b-.gif 805 23
tshirt35 ౩౫౦ 9df27e4-.gif 402.5 35
jeans30-32 ౬౦౦ 47d975c-.gif 690 50
jack1 ౮౦౦ 4698ee8-.gif 920 25


i want to display this like


jack1 ౮౦౦ 4698ee8-.gif 920
jack1 ౮౦౦ 4698ee8-.gif 920
jack1 ౮౦౦ 4698ee8-.gif 920
jack1 ౮౦౦ 4698ee8-.gif 920
jack1 ౮౦౦ 4698ee8-.gif 920
jack1 ౮౦౦ 4698ee8-.gif 920 means 25 times

Means row repeat as per the number of quantity

推荐答案

使用Go语句：



https://msdn.microsoft.com/en-us/library/ms188037.aspx [ ^ ]

Use Go Statement:

https://msdn.microsoft.com/en-us/library/ms188037.aspx[^]

您还没有提到您正在使用的DBMS。假设最近版本的Microsoft SQL Server，这很容易解决。



你需要一个计数表 - 如果你还没有，有很多方法可以生成一个。例如：

SQL Wizardry Part Eight - Tally Tables [ ^ ]



一旦你有了这个，一个简单的 INNER JOIN 就能得到你想要的结果： br />

You haven't mentioned which DBMS you're using. Assuming a recent version of Microsoft SQL Server, this is fairly easy to solve.

You'll need a "tally table" - if you don't have one already, there are many ways to generate one. For example:
SQL Wizardry Part Eight - Tally Tables[^]

Once you have that, a simple INNER JOIN will give you the desired result:

WITH E1 (N) AS 
(
    -- 1*10^1 or 10 rows
    SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1 
    UNION ALL SELECT 1
),
E2 (N) AS 
(
    -- 1*10^2 or 100 rows
    SELECT 1 
    FROM E1 a, E1 b
),
E4 (N) AS 
(
    -- 1*10^4 or 10000 rows
    SELECT 1 
    FROM E2 a, E2 b
),
Tally (N) AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM E4
)
SELECT
    D.Name,
    D.Telugu,
    D.Image,
    D.price
FROM
    YourTable As D
    INNER JOIN Tally As T
    ON T.N <= D.quan
ORDER BY
    D.Name
;

你可以试试。

You can try that.

create table #InputData (col1 varchar(100), col2 int);
insert into #InputData values('jack1 ౮౦౦ 4698ee8-.gif 920', 25);
insert into #InputData values('jeans30-32 ౬౦౦ 47d975c-.gif 690', 50);
insert into #InputData values('tshirt35 ౩౫౦ 9df27e4-.gif 402.5', 35);
insert into #InputData values('jaen123 ౭౦౦ bcb6a0b-.gif 805', 23);

Declare RestCursor cursor for select col1, col2 from #InputData;
Declare @Col1 varchaR(100);
Declare @Col2 int;
Declare @Count int;

create table #ResultTable (col varchar(100));
open RestCursor;
fetch next from RestCursor into @Col1, @Col2;

while @@FETCH_STATUS = 0 begin
    set @Count = 0;
    while @Count < @Col2 begin
        insert into #ResultTable values(@Col1);
        set @Count = @Count + 1;
    end;
    fetch next from RestCursor into @Col1, @Col2;
end;
close RestCursor;
deallocate RestCursor;

select * from #ResultTable;

drop table #ResultTable;
drop table #InputData;

这篇关于SQl查询重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！