表1是消费项目表
CREATE TABLE [dbo].[T_Account_Items](
[id] [int] IDENTITY(1,1) NOT NULL,
[Account_No] [varchar](50) NULL,
[Itme_Id] [int] NULL,
[Service_Amount] [float] NULL,
[Price] [numeric](12, 2) NULL,
[Pay_Price] [numeric](12, 2) NULL,
[Room_Id] [int] NULL,
[Waiter_Id] [int] NULL,
[Artificer_Id1] [int] NULL,
[Men] [text] NULL,
[Staff_Id] [int] NULL,
[LastUpdate_Time] [datetime] NULL,
[Artificer_Id2] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFF
GO
表2是员工表
CREATE TABLE [dbo].[T_Employees](
[id] [int] IDENTITY(1,1) NOT NULL,
[Employee_Id] [varchar](10) NOT NULL,
[Employee_Name] [varchar](20) NOT NULL,
[Work_Type] [varchar](2) NULL,
[DepartMent_Id] [int] NULL,
[Extension_Phone] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[Birthday] [datetime] NULL,
[Phone] [nvarchar](20) NULL,
[status] [varchar](2) NULL,
CONSTRAINT [PK_T_Employees] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]GO我用
SELECT A.Account_No,A.Price, E.Employee_Name as 技师1,D.Employee_Name AS 技师2
FROM T_Account_Items AS A, T_Employees AS E,T_Employees as DWHERE A.Artificer_Id1=E.Employee_Id and A.Artificer_Id2=D.Employee_Id and Account_No='3201'
这个的话得不到技师为空的数据想查询得到这样的结果(能得到技师2为空的)
Account_No,Price ,技师1,技师2
3201 1 aa null
3201 2 aa bb (如果技师2有的话就输入没有的显示空)
CREATE TABLE [dbo].[T_Account_Items](
[id] [int] IDENTITY(1,1) NOT NULL,
[Account_No] [varchar](50) NULL,
[Itme_Id] [int] NULL,
[Service_Amount] [float] NULL,
[Price] [numeric](12, 2) NULL,
[Pay_Price] [numeric](12, 2) NULL,
[Room_Id] [int] NULL,
[Waiter_Id] [int] NULL,
[Artificer_Id1] [int] NULL,
[Men] [text] NULL,
[Staff_Id] [int] NULL,
[LastUpdate_Time] [datetime] NULL,
[Artificer_Id2] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFF
GO
表2是员工表
CREATE TABLE [dbo].[T_Employees](
[id] [int] IDENTITY(1,1) NOT NULL,
[Employee_Id] [varchar](10) NOT NULL,
[Employee_Name] [varchar](20) NOT NULL,
[Work_Type] [varchar](2) NULL,
[DepartMent_Id] [int] NULL,
[Extension_Phone] [varchar](50) NULL,
[Address] [varchar](50) NULL,
[Birthday] [datetime] NULL,
[Phone] [nvarchar](20) NULL,
[status] [varchar](2) NULL,
CONSTRAINT [PK_T_Employees] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]GO我用
SELECT A.Account_No,A.Price, E.Employee_Name as 技师1,D.Employee_Name AS 技师2
FROM T_Account_Items AS A, T_Employees AS E,T_Employees as DWHERE A.Artificer_Id1=E.Employee_Id and A.Artificer_Id2=D.Employee_Id and Account_No='3201'
这个的话得不到技师为空的数据想查询得到这样的结果(能得到技师2为空的)
Account_No,Price ,技师1,技师2
3201 1 aa null
3201 2 aa bb (如果技师2有的话就输入没有的显示空)
解决方案 »
- SQL Server 建立连接时出现与网络相关的或特定于实例的错误
- @using MvcPageTest.Models
- 返回用户实体类,可以用dataset代替吗?
- 一个简单问题!请大家帮忙!!
- 通配符
- 关于传递参数中有多个"#"号的问题.很怪.求各位高手帮忙看看.
- datagrid 中动态生成的按钮列的SelectedIndexChanged 事件的奇怪现象,请各位大侠帮忙分析一下,见者有分
- 報表不顯示圖片的問題丫丫
- 关于javascrip 取子字符串函数
- 为什么panel已设了enable=false,panel里包含的linkbutton还可以点击?
- JQ取值问题。。。。
- jQuery ajax在IE下执行异常而在FireFox下执行部分正常怎么办?
select A.Account_No,A.Price,技师1,技师2 from T_Account_Items A left join
(select Employee_Id,Employee_Name as 技师1 from T_Employees ) E on A.Artificer_Id1=E.Employee_Id
left join (select Employee_Id,Employee_Name as 技师2 from T_Employees ) D on A.Artificer_Id2=D.Employee_Id where Account_No='3201'