菜鸟询问个sql查询命令
本帖最后由 leon118 于 2013-10-24 09:37:36 编辑 菜鸟询问个sql查询命令
数据库如下：
item quality
000101001c
000101002c
000101003c
000101004c
000101005f
000101006f
000102001f
000102002f
000102003c
000102004c
000102005c
000102006f
000102007c
000202001f
000202002c
000202003f
000202004c
000202005c
000202006c
000202007f
我想查询当quality=c时，0001开头的结果。
谢谢！ sql
[解决办法]
select * from tablename
where quality='c' and item like '0001%'
[解决办法]
select *
from [表名]
where quality='c' and left(item,4)='0001'
select * from [表名]
where quality='c' and substring(item,1,4)='0001'
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-10-24 10:40:32
-- 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: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([item] varchar(9),[quality] varchar(1))
insert [huang]
select '000101001','c' union all
select '000101002','c' union all
select '000101003','c' union all
select '000101004','c' union all
select '000101005','f' union all
select '000101006','f' union all
select '000102001','f' union all
select '000102002','f' union all
select '000102003','c' union all
select '000102004','c' union all
select '000102005','c' union all
select '000102006','f' union all
select '000102007','c' union all
select '000202001','f' union all
select '000202002','c' union all
select '000202003','f' union all
select '000202004','c' union all
select '000202005','c' union all
select '000202006','c' union all
select '000202007','f'
--------------开始查询--------------------------
select * from [huang] WHERE quality='c' AND LEFT([item],4)='0001'
----------------结果----------------------------
/*
item quality
--------- -------
000101001 c
000101002 c
000101003 c
000101004 c
000102003 c
000102004 c
000102005 c
000102007 c
*/
