select a.*,b.餐次名称
from 消费记录表 a
left join 餐次表 b on convert(varchar(5),a.消费时间,108) between b.开始时间 and b.结束时间试下,这样关联查询有什么问题吗
from 消费记录表 a
left join 餐次表 b on convert(varchar(5),a.消费时间,108) between b.开始时间 and b.结束时间试下,这样关联查询有什么问题吗
解决方案 »
- 头昏脑胀欲哭无泪~~跪求高手指点菜鸟SQL语句!
- 帮我看一个存储过程,有一点不明白的
- 将某个表的记录行,当成是列的问题,不知道说的是否明白,大家进来看下
- 在SQL 语句中加入条件判断的问题
- 基于SQL Server的应用程序,是不是在应用服务器上一定要安装SQL Server?
- 求助在查询管理器或管理工具中的一个问题.
- sql server写查询northwind数据库每个季度的销售情况
- 数据排序问题
- >请问,SQL 个人版,安装时,到了一大半,出现提示??<
- 求救:MySQL安装失败……
- 处理时间段SQL
- 求大神帮忙!急!急!急!sql2008评估期已过,在网上查阅说让升级,但是在升级时遇到值不能为空,参数名:doc错误,求教!
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-02 15:31:44
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([EMPID] varchar(5),[EMPNAME] varchar(4),[TOLLDATETIME] datetime)
insert [T1]
select '00001','张三','2013-11-10 18:30:30' union all
select '00002','李四','2013-11-09 11:55:20' union all
select '00003','王五','2013-11-08 22:20:25' union all
select '00004','赵六','2013-11-08 07:30:21'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([TOLLNAME] varchar(4),[STARTTIME] VARCHAR(5) ,[ENDTIME] VARCHAR(5) )
insert [T2]
select '早餐','07:00','08:30' union all
select '午餐','10:30','12:00' union all
select '晚餐','17:00','20:00'
--------------开始查询--------------------------
select t1.*,ISNULL( t2.tollname,'其他时段')餐次类型
from [T1] left JOIN [t2] on CONVERT(VARCHAR(5),t1.[TOLLDATETIME],108) BETWEEN CONVERT(VARCHAR(5),[t2].[STARTTIME]) AND CONVERT(VARCHAR(5),[t2].[ENDTIME])
----------------结果----------------------------
/*
EMPID EMPNAME TOLLDATETIME 餐次类型
----- ------- ----------------------- ----
00001 张三 2013-11-10 18:30:30.000 晚餐
00002 李四 2013-11-09 11:55:20.000 午餐
00003 王五 2013-11-08 22:20:25.000 其他
00004 赵六 2013-11-08 07:30:21.000 早餐*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-02 15:31:44
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
-- Dec 28 2012 20:23:12
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:[T1]
if object_id('[T1]') is not null drop table [T1]
go
create table [T1]([EMPID] varchar(5),[EMPNAME] varchar(4),[TOLLDATETIME] datetime)
insert [T1]
select '00001','张三','2013-11-10 18:30:30' union all
select '00002','李四','2013-11-09 11:55:20' union all
select '00003','王五','2013-11-08 22:20:25' union all
select '00004','赵六','2013-11-08 07:30:21'
--> 测试数据:[T2]
if object_id('[T2]') is not null drop table [T2]
go
create table [T2]([TOLLNAME] varchar(4),[STARTTIME] VARCHAR(5) ,[ENDTIME] VARCHAR(5) )
insert [T2]
select '早餐','07:00','08:30' union all
select '午餐','10:30','12:00' union all
select '晚餐','17:00','20:00'
--------------开始查询--------------------------
select t1.*,CASE WHEN t2.tollname IS NULL THEN '其他时段' ELSE t2.tollname END 餐次类型
from [T1] left JOIN [t2] on CONVERT(VARCHAR(5),t1.[TOLLDATETIME],108) BETWEEN CONVERT(VARCHAR(5),[t2].[STARTTIME]) AND CONVERT(VARCHAR(5),[t2].[ENDTIME])
----------------结果----------------------------
/*
EMPID EMPNAME TOLLDATETIME 餐次类型
----- ------- ----------------------- --------
00001 张三 2013-11-10 18:30:30.000 晚餐
00002 李四 2013-11-09 11:55:20.000 午餐
00003 王五 2013-11-08 22:20:25.000 其他时段
00004 赵六 2013-11-08 07:30:21.000 早餐
*/