if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Order_Detail_Order_Info]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[order_detail] DROP CONSTRAINT FK_Order_Detail_Order_Info
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[order_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[order_info]
GO
--第一个表
CREATE TABLE [dbo].[order_info] (
[Order_Id] [int] NOT NULL ,
[Order_Number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Order_User] [int] NOT NULL ,
[Order_Date] [datetime] NULL ,
[Order_Moth] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--第二个表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[order_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[order_detail]
GO CREATE TABLE [dbo].[order_detail] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Id] [int] NOT NULL ,
[Product_user_id] [int] NOT NULL ,
[Product_Number] [int] NULL ,
[Product_Price] [money] NULL ,
[Order_Status] [bit] NOT NULL ,
[Service_Id] [int] NOT NULL ,
[Product_Total_Price] [money] NULL
) ON [PRIMARY]
GO --怎么才能去除重复项
select top 10 * from order_info,order_detail
where order_info.Order_Id not in(select top 0 order_info.Order_Id from order_info,order_detail where order_info.Order_Id=order_detail.Order_Id order by order_info.Order_Id desc) and order_info.Order_Id=order_detail.Order_Id order by order_info.Order_Id
--怎么去除重复项。现在是一个订单号对应多条记录。怎么才能去除重复 order_info.Order_Id为主键对应order_detail。Order_Id的外键
ALTER TABLE [dbo].[order_detail] DROP CONSTRAINT FK_Order_Detail_Order_Info
GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[order_info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[order_info]
GO
--第一个表
CREATE TABLE [dbo].[order_info] (
[Order_Id] [int] NOT NULL ,
[Order_Number] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[Order_User] [int] NOT NULL ,
[Order_Date] [datetime] NULL ,
[Order_Moth] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--第二个表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[order_detail]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[order_detail]
GO CREATE TABLE [dbo].[order_detail] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Order_Id] [int] NOT NULL ,
[Product_user_id] [int] NOT NULL ,
[Product_Number] [int] NULL ,
[Product_Price] [money] NULL ,
[Order_Status] [bit] NOT NULL ,
[Service_Id] [int] NOT NULL ,
[Product_Total_Price] [money] NULL
) ON [PRIMARY]
GO --怎么才能去除重复项
select top 10 * from order_info,order_detail
where order_info.Order_Id not in(select top 0 order_info.Order_Id from order_info,order_detail where order_info.Order_Id=order_detail.Order_Id order by order_info.Order_Id desc) and order_info.Order_Id=order_detail.Order_Id order by order_info.Order_Id
--怎么去除重复项。现在是一个订单号对应多条记录。怎么才能去除重复 order_info.Order_Id为主键对应order_detail。Order_Id的外键
删除数据库中重复记录
top 0
如果你要去除重复行一个记录都不保留 先分组找出行数大于1的记录,然后删除该记录就可以了。
select top 10 * from order_info,order_detail
where order_info.Order_Id not in(select top 1 distinct order_info.Order_Id from order_info,order_detail where order_info.Order_Id=order_detail.Order_Id order by order_info.Order_Id desc) and order_info.Order_Id=order_detail.Order_Id order by order_info.Order_Id
参考