消息 245，级别 16，状态 1，第 1 行 在将 varchar 值 'A' 转换成数据类型 int 时失败。

[囧]

select count(1) from db_news 
where city in( 
	select [value] from db_param where [id] in(5544,5570,5588,5606,5628,5654,5680,5733,5751,5772,5800,5860,5882,5895,5909,5939,5955,5992,6031,6049,6101,6135,6178,6208,6237,6075,6093,6147,6183,6221,6267,6284,6309,6322,6347,6384,6407,6424,6441,6456,6471,6488,6503,6520,6537,6548,6565,6582,18,33,46,59,6621,6642,6657,6668,6671,6718,6767,6814,6861,6886,6927,6954,7005)
) and ctime >= '2018-05-11'

上面为出错的sql代码，奇怪的地方在于用单个条件或改下日期条件是不会出错的

把 and ctime >= '2018-05-11' 去掉，正确，结果：79602
把 '2018-05-11' 改成 '2017-05-11' ，正确，结果：7403
把 '2018-05-11' 改成 '2019-05-11' ，正确，结果：0
把 city in(...)去掉 ，正确，结果：53868
把 子查询 [id] in(5544,5570,5588...id列表) 中任意一个id去掉，正确，结果：0

为什么会这样。

[卖水果的net]

引用 4 楼 Free_Wind22 的回复:

[quote=引用 2 楼 wmxcn2000 的回复:] 这个是数据的问题，如果数量不大，你把数据，都贴上来，用 insert 形式的。

２个表，总数据有50W+[/quote] 那就把两张表的结构说一下，要 create table 形式的。

[二月十六]

SELECT COUNT(1)
FROM
(
    SELECT *
    FROM db_news
    WHERE city IN (
                      SELECT [value]
                      FROM db_param
                      WHERE [id] IN ( 5544, 5570, 5588, 5606, 5628, 5654, 5680, 5733, 5751, 5772, 5800, 5860, 5882,
                                      5895, 5909, 5939, 5955, 5992, 6031, 6049, 6101, 6135, 6178, 6208, 6237, 6075,
                                      6093, 6147, 6183, 6221, 6267, 6284, 6309, 6322, 6347, 6384, 6407, 6424, 6441,
                                      6456, 6471, 6488, 6503, 6520, 6537, 6548, 6565, 6582, 18, 33, 46, 59, 6621, 6642,
                                      6657, 6668, 6671, 6718, 6767, 6814, 6861, 6886, 6927, 6954, 7005
                                    )
                  )
) t
WHERE ctime >= '2018-05-11';

[shinger126]

ID字段是什么类型？

[唐诗三百首]

请提供db_param表和db_news表的表结构.

[zbdzjx]

执行一下这句，看看结果是什么。

select distinct [value] from db_param where [id] in(5544,5570,5588,5606,5628,5654,5680,5733,5751,5772,5800,5860,5882,5895,5909,5939,5955,5992,6031,6049,6101,6135,6178,6208,6237,6075,6093,6147,6183,6221,6267,6284,6309,6322,6347,6384,6407,6424,6441,6456,6471,6488,6503,6520,6537,6548,6565,6582,18,33,46,59,6621,6642,6657,6668,6671,6718,6767,6814,6861,6886,6927,6954,7005)

[zjcxc]

真的看表结构就清楚了，何必让大家猜 之所以在不同的写法和条件下，有的正确，有的不正确，有可能是要转换的数据恰好在转换前就被过滤掉 了，毕竟不同的条件和写法的执行顺序不一定一样，要扫描的数据也不尽相同

[卖水果的net]

这个是数据的问题，如果数量不大，你把数据，都贴上来，用 insert 形式的。

[囧]

引用 2 楼 wmxcn2000 的回复:

这个是数据的问题，如果数量不大，你把数据，都贴上来，用 insert 形式的。

２个表，总数据有50W+

[唐诗三百首]

with t as
(select [value]=cast([value] as int)
 from db_param 
 where [id] in(5544,5570,5588,5606,5628,5654,5680,5733,5751,5772,5800,5860,5882,5895,5909,5939,5955,5992,6031,6049,6101,6135,6178,6208,6237,6075,6093,6147,6183,6221,6267,6284,6309,6322,6347,6384,6407,6424,6441,6456,6471,6488,6503,6520,6537,6548,6565,6582,18,33,46,59,6621,6642,6657,6668,6671,6718,6767,6814,6861,6886,6927,6954,7005)
)
select count(1)
 from db_news 
 where city in(select [value] from t) 
 and ctime>='2018-05-11'

[囧]

引用 1 楼 ap0405140 的回复:

with t as
(select [value]=cast([value] as int)
 from db_param 
 where [id] in(5544,5570,5588,5606,5628,5654,5680,5733,5751,5772,5800,5860,5882,5895,5909,5939,5955,5992,6031,6049,6101,6135,6178,6208,6237,6075,6093,6147,6183,6221,6267,6284,6309,6322,6347,6384,6407,6424,6441,6456,6471,6488,6503,6520,6537,6548,6565,6582,18,33,46,59,6621,6642,6657,6668,6671,6718,6767,6814,6861,6886,6927,6954,7005)
)
select count(1)
 from db_news 
 where city in(select [value] from t) 
 and ctime>='2018-05-11'

还是同样的错误　

[囧]

CREATE TABLE [dbo].[DB_News](
	[nID] [int] IDENTITY(1,1) NOT NULL,
	[province] [int] NULL,
	[city] [int] NULL,
	[topic] [nvarchar](250) NULL,
	[source] [int] NULL,
	[industry] [nvarchar](20) NULL,
	[price] [decimal](18, 2) NULL,
	[startTime] [date] NULL,
	[endTime] [date] NULL,
	[cTime] [date] NULL,
	[content] [ntext] NULL,
 CONSTRAINT [PK_DB_News] PRIMARY KEY CLUSTERED 
(
	[nID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[DB_Param](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[group] [int] NULL,
	[value] [varchar](50) NULL,
	[name] [nvarchar](50) NULL
 CONSTRAINT [PK_DB_Area] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

这个是表结构

[囧]

引用 6 楼 sinat_28984567 的回复:

SELECT COUNT(1)
FROM
(
    SELECT *
    FROM db_news
    WHERE city IN (
                      SELECT [value]
                      FROM db_param
                      WHERE [id] IN ( 5544, 5570, 5588, 5606, 5628, 5654, 5680, 5733, 5751, 5772, 5800, 5860, 5882,
                                      5895, 5909, 5939, 5955, 5992, 6031, 6049, 6101, 6135, 6178, 6208, 6237, 6075,
                                      6093, 6147, 6183, 6221, 6267, 6284, 6309, 6322, 6347, 6384, 6407, 6424, 6441,
                                      6456, 6471, 6488, 6503, 6520, 6537, 6548, 6565, 6582, 18, 33, 46, 59, 6621, 6642,
                                      6657, 6668, 6671, 6718, 6767, 6814, 6861, 6886, 6927, 6954, 7005
                                    )
                  )
) t
WHERE ctime >= '2018-05-11';

这个也是同样的错误

[shinger126]

select * from db_param where [value]='A'有没有结果？

[囧]

引用 13 楼 shinger126 的回复:

select * from db_param where [value]='A'有没有结果？

like 'A%'有结果