sql报表生成重复行

agyaoht7 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(340)

我对sql报表编写还不太熟悉，尝试从sql表生成报表，但结果中每个所需行对应3个重复行。但是，输出数据符合我的要求。下面给出了完整的代码，请帮助我解决。

SELECT 
   v.dateaccessioned AS 'Date', v.barcode AS 'Acc. No.', w.title AS 'Title',
   w.author AS 'Author/Editor', concat_ws(' , ', o.editionstatement, 
   v.enumchron) AS 'Ed./Vol.',
   concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher',
   w.copyrightdate AS 'Year', o.pages AS 'Page(s)', s.name AS 'Source',
   v.itemcallnumber AS 'Class No./Book No.', concat_ws(', ₹', concat(' ',
   z.symbol, x.listprice), x.rrp_tax_included) AS 'Cost', concat_ws(' , ',
   r.invoicenumber, r.shipmentdate) AS 'Bill No. & Date', '' AS 'Withdrawn 
   Date', '' AS 'Remarks'

FROM biblioitems o
LEFT JOIN items v ON v.biblioitemnumber=o.biblioitemnumber
LEFT JOIN biblio w ON w.biblionumber=o.biblionumber
LEFT JOIN aqorders x ON x.biblionumber=o.biblionumber
LEFT JOIN currency z ON z.currency=x.currency
LEFT JOIN aqinvoices r ON r.booksellerid=v.booksellerid
LEFT JOIN aqbooksellers s ON s.id=v.booksellerid

WHERE v.barcode BETWEEN <<Accession Number>> AND <<To Accession Number>>
ORDER BY v.barcode ASC

sql mysql duplicates

来源：https://stackoverflow.com/questions/51941804/sql-report-generate-duplicate-rows

3条答案

按热度按时间

ivqmmu1c1#

你要么分开，要么分组。在下面的代码中，我使用了groupby。

SELECT 
v.dateaccessioned AS 'Date'
, v.barcode AS 'Acc. No.'
, w.title AS 'Title'
, w.author AS 'Author/Editor'
, concat_ws(' , ', o.editionstatement,     v.enumchron) AS 'Ed./Vol.'
,    concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher'
,    w.copyrightdate AS 'Year', o.pages AS 'Page(s)', s.name AS 'Source'
,    v.itemcallnumber AS 'Class No./Book No.'
, concat_ws(', ₹', concat(' ',     z.symbol, x.listprice), x.rrp_tax_included) AS 'Cost'
, concat_ws(' , ', r.invoicenumber, r.shipmentdate) AS 'Bill No. & Date'
, '' AS 'Withdrawn Date'
, '' AS 'Remarks'

FROM biblioitems o
LEFT JOIN items v ON v.biblioitemnumber=o.biblioitemnumber
LEFT JOIN biblio w ON w.biblionumber=o.biblionumber
LEFT JOIN aqorders x ON x.biblionumber=o.biblionumber
LEFT JOIN currency z ON z.currency=x.currency
LEFT JOIN aqinvoices r ON r.booksellerid=v.booksellerid
LEFT JOIN aqbooksellers s ON s.id=v.booksellerid

WHERE v.barcode BETWEEN <<Accession Number>> AND <<To Accession Number>>
GROUP BY 
v.dateaccessioned 
, v.barcode 
, w.title 
,w.author 
, concat_ws(' , ', o.editionstatement, v.enumchron) 
,concat_ws(' ', o.place, o.publishercode) 
,w.copyrightdate 
, o.pages 
, s.name 
,v.itemcallnumber
, concat_ws(', ₹', concat(' ', z.symbol, x.listprice), x.rrp_tax_included) 
, concat_ws(' , ', r.invoicenumber, r.shipmentdate)
ORDER BY v.barcode ASC

赞(0）回复(0）举报 2021-06-20

7nbnzgx92#

如果行相同，则可以使用如下所示的distinct关键字：

SELECT distinct
v.dateaccessioned AS 'Date', v.barcode AS 'Acc. No.', w.title AS 'Title',
w.author AS 'Author/Editor', concat_ws(' , ', o.editionstatement, 
v.enumchron) AS 'Ed./Vol.',
concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher',
w.copyrightdate AS 'Year', o.pages AS 'Page(s)', s.name AS 'Source',
v.itemcallnumber AS 'Class No./Book No.', concat_ws(', ₹', concat(' ', 

z.symbol, x.listprice), x.rrp_tax_included) AS 'Cost', concat_ws(' , ', 

r.invoicenumber, r.shipmentdate) AS 'Bill No. & Date', '' AS 'Withdrawn 

Date', '' AS 'Remarks'

FROM biblioitems o
LEFT JOIN items v ON v.biblioitemnumber=o.biblioitemnumber
LEFT JOIN biblio w ON w.biblionumber=o.biblionumber
LEFT JOIN aqorders x ON x.biblionumber=o.biblionumber
LEFT JOIN currency z ON z.currency=x.currency
LEFT JOIN aqinvoices r ON r.booksellerid=v.booksellerid
LEFT JOIN aqbooksellers s ON s.id=v.booksellerid

WHERE v.barcode BETWEEN <<Accession Number>> AND <<To Accession Number>>
ORDER BY v.barcode ASC

赞(0）回复(0）举报 2021-06-20

qoefvg9y3#

我发现了一个诡计就成功了。v、条形码是唯一的记录，所以我刚刚添加
按v.barcode分组
每个记录只能得到一行。

赞(0）回复(0）举报 2021-06-20