找出重复值的SQL语句
如:venderno 有重复的列表,这里只指定了一天.
语句如下
select venderno from po where pdate='2003-9-3' group by venderno having count(*) >=2
进一步应用
SELECT vno,shortname FROM vendor WHERE vno in(select venderno from po where pdate='2003-9-3' group by venderno having count(*) >=2)
表的脚本如下:
CREATE TABLE [dbo].[po] (
[pno] varchar(12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[pdate] smalldatetime,
[venderno] varchar(6) COLLATE Chinese_PRC_CI_AS,
[vender] varchar(30) COLLATE Chinese_PRC_CI_AS,
[payment] varchar(10) COLLATE Chinese_PRC_CI_AS,
[shipaddress] varchar(40) COLLATE Chinese_PRC_CI_AS,
[currency] varchar(10) COLLATE Chinese_PRC_CI_AS,
[amount] float,
[rate] float,
[rmbamount] float,
[remark] varchar(10) COLLATE Chinese_PRC_CI_AS,
[indate] smalldatetime,
[inuser] varchar(10) COLLATE Chinese_PRC_CI_AS,
[zldno] varchar(10) COLLATE Chinese_PRC_CI_AS,
[cuspo] varchar(20) COLLATE Chinese_PRC_CI_AS,
[itemno] varchar(20) COLLATE Chinese_PRC_CI_AS,
[unicheck] smallint,
[vendorcheck] smallint,
[vendordate] smalldatetime,
ON UPDATE CASCADE
ON DELETE NO ACTION
)
ON [PRIMARY]
GO
语句如下
select venderno from po where pdate='2003-9-3' group by venderno having count(*) >=2
进一步应用
SELECT vno,shortname FROM vendor WHERE vno in(select venderno from po where pdate='2003-9-3' group by venderno having count(*) >=2)
表的脚本如下:
CREATE TABLE [dbo].[po] (
[pno] varchar(12) COLLATE Chinese_PRC_CI_AS NOT NULL,
[pdate] smalldatetime,
[venderno] varchar(6) COLLATE Chinese_PRC_CI_AS,
[vender] varchar(30) COLLATE Chinese_PRC_CI_AS,
[payment] varchar(10) COLLATE Chinese_PRC_CI_AS,
[shipaddress] varchar(40) COLLATE Chinese_PRC_CI_AS,
[currency] varchar(10) COLLATE Chinese_PRC_CI_AS,
[amount] float,
[rate] float,
[rmbamount] float,
[remark] varchar(10) COLLATE Chinese_PRC_CI_AS,
[indate] smalldatetime,
[inuser] varchar(10) COLLATE Chinese_PRC_CI_AS,
[zldno] varchar(10) COLLATE Chinese_PRC_CI_AS,
[cuspo] varchar(20) COLLATE Chinese_PRC_CI_AS,
[itemno] varchar(20) COLLATE Chinese_PRC_CI_AS,
[unicheck] smallint,
[vendorcheck] smallint,
[vendordate] smalldatetime,
ON UPDATE CASCADE
ON DELETE NO ACTION
)
ON [PRIMARY]
GO