我是3张表 1个视图相关联
结构如下
BBS_Sendnote 表:
CREATE TABLE [dbo].[BBS_Sendnote](
[SendnoteID] [int] IDENTITY(1,1) NOT NULL,
[SendnoteName] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[NoteContent] [text] COLLATE Chinese_PRC_CI_AS NOT NULL,
[Writer] [int] NULL,
[Createdata] [datetime] NULL CONSTRAINT [DF_BBS_Sendnote_Createdata] DEFAULT (getdate()),
[SmallClassID] [int] NULL,
[TypeID] [int] NULL CONSTRAINT [DF_BBS_Sendnote_TypeID] DEFAULT ((1)),
[ImgType] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_BBS_Sendnote_ImgType] DEFAULT (''),
[StateID] [int] NULL CONSTRAINT [DF_BBS_Sendnote_StateID] DEFAULT ((1)),
[NoteOrder] [int] NULL CONSTRAINT [DF_BBS_Sendnote_NoteOrder] DEFAULT ((0)),
[LastBackUserID] [int] NULL CONSTRAINT [DF_BBS_Sendnote_LastBackUserID] DEFAULT ((0)),
[LastBackUserDate] [datetime] NULL CONSTRAINT [DF_BBS_Sendnote_LastBackUserDate] DEFAULT (getdate()),
[Scannum] [int] NULL CONSTRAINT [DF_BBS_Sendnote_Scannum] DEFAULT ((0)),
[Recenum] [int] NULL CONSTRAINT [DF_BBS_Sendnote_Recenum] DEFAULT ((0))
)
BBS_Res 表:
CREATE TABLE [dbo].[BBS_Res](
[ResID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[Time] [datetime] NOT NULL CONSTRAINT [DF_BBS_Res_Time] DEFAULT (getdate()),
[ResName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ResRoot] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[PostID] [int] NOT NULL,
[LoadCount] [int] NULL CONSTRAINT [DF_BBS_Res_LoadCount] DEFAULT ((0)))
BBS_PostType 表:
CREATE TABLE [dbo].[BBS_PostType](
[PostTypeID] [int] IDENTITY(1,1) NOT NULL,
[PostTypeName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[PostTypeMenuSmallId] [int] NULL CONSTRAINT [DF_Bbs_PostType_PostType_MenuBigId] DEFAULT ((0)),
[PostTypeAdddate] [datetime] NULL CONSTRAINT [DF_Bbs_PostType_PostType_Adddate] DEFAULT (getdate()))
一个视图表
太多了 就写用到的几列吧
BBSI, UserName,Email
我用链接的语句将上面的几个表和视图链接起来
出现 一条记录出现2次,而且是一样的。链接语句是:SELECT dbo.BBS_Sendnote.SendnoteID, dbo.BBS_Sendnote.SendnoteName, dbo.BBS_Sendnote.NoteContent, dbo.BBS_Sendnote.Writer, dbo.V_Bbs_UserDetail.UserName, dbo.V_Bbs_UserDetail.Email, dbo.V_Bbs_UserDetail.CreateDate, dbo.V_Bbs_UserDetail.Sex, dbo.V_Bbs_UserDetail.BornTime, dbo.V_Bbs_UserDetail.Address,dbo.V_Bbs_UserDetail.QICQ,dbo.V_Bbs_UserDetail.FaceImg,dbo.V_Bbs_UserDetail.PersonSign, dbo.V_Bbs_UserDetail.SendNum, dbo.V_Bbs_UserDetail.ReceiveNum, dbo.V_Bbs_UserDetail.ClassicCount, dbo.V_Bbs_UserDetail.Mark, dbo.V_Bbs_UserDetail.LevelName, dbo.V_Bbs_UserDetail.Img, dbo.BBS_Sendnote.Createdata, dbo.BBS_Sendnote.SmallClassID, dbo.BBS_Sendnote.TypeID, dbo.BBS_PostType.PostTypeName, dbo.BBS_Sendnote.Scannum, dbo.BBS_Res.ResName, dbo.BBS_Res.LoadCount, dbo.BBS_Res.ResRoot, dbo.BBS_Res.ResID
FROM dbo.BBS_Sendnote LEFT OUTER JOIN dbo.BBS_Res ON dbo.BBS_Sendnote.SendnoteID = dbo.BBS_Res.PostID LEFT OUTER JOIN dbo.BBS_PostType ON dbo.BBS_Sendnote.TypeID = dbo.BBS_PostType.PostTypeID LEFT OUTER JOIN
dbo.V_Bbs_UserDetail ON dbo.BBS_Sendnote.Writer = dbo.V_Bbs_UserDetail.BBSID
这个是怎么回事?谢谢!
结构如下
BBS_Sendnote 表:
CREATE TABLE [dbo].[BBS_Sendnote](
[SendnoteID] [int] IDENTITY(1,1) NOT NULL,
[SendnoteName] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
[NoteContent] [text] COLLATE Chinese_PRC_CI_AS NOT NULL,
[Writer] [int] NULL,
[Createdata] [datetime] NULL CONSTRAINT [DF_BBS_Sendnote_Createdata] DEFAULT (getdate()),
[SmallClassID] [int] NULL,
[TypeID] [int] NULL CONSTRAINT [DF_BBS_Sendnote_TypeID] DEFAULT ((1)),
[ImgType] [varchar](100) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_BBS_Sendnote_ImgType] DEFAULT (''),
[StateID] [int] NULL CONSTRAINT [DF_BBS_Sendnote_StateID] DEFAULT ((1)),
[NoteOrder] [int] NULL CONSTRAINT [DF_BBS_Sendnote_NoteOrder] DEFAULT ((0)),
[LastBackUserID] [int] NULL CONSTRAINT [DF_BBS_Sendnote_LastBackUserID] DEFAULT ((0)),
[LastBackUserDate] [datetime] NULL CONSTRAINT [DF_BBS_Sendnote_LastBackUserDate] DEFAULT (getdate()),
[Scannum] [int] NULL CONSTRAINT [DF_BBS_Sendnote_Scannum] DEFAULT ((0)),
[Recenum] [int] NULL CONSTRAINT [DF_BBS_Sendnote_Recenum] DEFAULT ((0))
)
BBS_Res 表:
CREATE TABLE [dbo].[BBS_Res](
[ResID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NOT NULL,
[Time] [datetime] NOT NULL CONSTRAINT [DF_BBS_Res_Time] DEFAULT (getdate()),
[ResName] [varchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[ResRoot] [varchar](100) COLLATE Chinese_PRC_CI_AS NOT NULL,
[PostID] [int] NOT NULL,
[LoadCount] [int] NULL CONSTRAINT [DF_BBS_Res_LoadCount] DEFAULT ((0)))
BBS_PostType 表:
CREATE TABLE [dbo].[BBS_PostType](
[PostTypeID] [int] IDENTITY(1,1) NOT NULL,
[PostTypeName] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[PostTypeMenuSmallId] [int] NULL CONSTRAINT [DF_Bbs_PostType_PostType_MenuBigId] DEFAULT ((0)),
[PostTypeAdddate] [datetime] NULL CONSTRAINT [DF_Bbs_PostType_PostType_Adddate] DEFAULT (getdate()))
一个视图表
太多了 就写用到的几列吧
BBSI, UserName,Email
我用链接的语句将上面的几个表和视图链接起来
出现 一条记录出现2次,而且是一样的。链接语句是:SELECT dbo.BBS_Sendnote.SendnoteID, dbo.BBS_Sendnote.SendnoteName, dbo.BBS_Sendnote.NoteContent, dbo.BBS_Sendnote.Writer, dbo.V_Bbs_UserDetail.UserName, dbo.V_Bbs_UserDetail.Email, dbo.V_Bbs_UserDetail.CreateDate, dbo.V_Bbs_UserDetail.Sex, dbo.V_Bbs_UserDetail.BornTime, dbo.V_Bbs_UserDetail.Address,dbo.V_Bbs_UserDetail.QICQ,dbo.V_Bbs_UserDetail.FaceImg,dbo.V_Bbs_UserDetail.PersonSign, dbo.V_Bbs_UserDetail.SendNum, dbo.V_Bbs_UserDetail.ReceiveNum, dbo.V_Bbs_UserDetail.ClassicCount, dbo.V_Bbs_UserDetail.Mark, dbo.V_Bbs_UserDetail.LevelName, dbo.V_Bbs_UserDetail.Img, dbo.BBS_Sendnote.Createdata, dbo.BBS_Sendnote.SmallClassID, dbo.BBS_Sendnote.TypeID, dbo.BBS_PostType.PostTypeName, dbo.BBS_Sendnote.Scannum, dbo.BBS_Res.ResName, dbo.BBS_Res.LoadCount, dbo.BBS_Res.ResRoot, dbo.BBS_Res.ResID
FROM dbo.BBS_Sendnote LEFT OUTER JOIN dbo.BBS_Res ON dbo.BBS_Sendnote.SendnoteID = dbo.BBS_Res.PostID LEFT OUTER JOIN dbo.BBS_PostType ON dbo.BBS_Sendnote.TypeID = dbo.BBS_PostType.PostTypeID LEFT OUTER JOIN
dbo.V_Bbs_UserDetail ON dbo.BBS_Sendnote.Writer = dbo.V_Bbs_UserDetail.BBSID
这个是怎么回事?谢谢!
解决方案 »
- 关于数据库权限的问题
- 高手帮忙看看sql出错的问题
- 这个SQL语句怎么写?
- 请各位前辈多多帮忙!谢谢!!!
- 如何提取商城中价格最低的数据,一个产品数据对应多个价格
- sql去重问题
- 请问这个存储过程如何写?
- 数据库链路问题,高手请进!!!
- SQLServer+VB6.0我通过ADO对象直接绑定到MSHFlexGrid控件,可是SQL中传来的日期格式是YYYY-MM-DD HH:MM:SS,我该怎么办,在显示时可以通过列宽来屏蔽,可是打印时我是用我自己的打印类,所见即所得,这样的话怎么办?
- DELPHI+MSSQL问题,各位大虾请进!
- excel表中数据导入到后缀名为gdb的文件中
- MS sql查询问题
FROM dbo.BBS_Sendnote LEFT OUTER JOIN dbo.BBS_Res ON dbo.BBS_Sendnote.SendnoteID = dbo.BBS_Res.PostID LEFT OUTER JOIN dbo.BBS_PostType ON dbo.BBS_Sendnote.TypeID = dbo.BBS_PostType.PostTypeID LEFT OUTER JOIN
dbo.V_Bbs_UserDetail ON dbo.BBS_Sendnote.Writer = dbo.V_Bbs_UserDetail.BBSID
如果 A.ID在B表中B.ID对应有两条记录,那么就会出现重复的。
加 distinct ...