下面给出部分数据
a b c d
2006-04-24PISBUC PIS 92 1
2006-04-24PISBUC BUC 74 0
2006-04-24NETPAC NET 88 1
2006-04-24NETPAC PAC 90 0
2006-04-24SUNLAK SUN 107 1
2006-04-24SUNLAK LAK 102 0
2006-04-24MAVGRI MAV 103 1
2006-04-24MAVGRI GRI 93 0
2006-04-25HEABUL HEA 115 1
2006-04-25HEABUL BUL 108 0
2006-04-25CLINUG CLI 98 1
其中a char(16),b char(3)(b的值是根据a的值生成的,值为right(a,3)或者substring(a,11,3))
c int ,d bit
现在要得出charindex('HEA',a)>0时,a的值一样时,b='HEA' 且d=1的c大于d=0的数目在线等待!!!
a b c d
2006-04-24PISBUC PIS 92 1
2006-04-24PISBUC BUC 74 0
2006-04-24NETPAC NET 88 1
2006-04-24NETPAC PAC 90 0
2006-04-24SUNLAK SUN 107 1
2006-04-24SUNLAK LAK 102 0
2006-04-24MAVGRI MAV 103 1
2006-04-24MAVGRI GRI 93 0
2006-04-25HEABUL HEA 115 1
2006-04-25HEABUL BUL 108 0
2006-04-25CLINUG CLI 98 1
其中a char(16),b char(3)(b的值是根据a的值生成的,值为right(a,3)或者substring(a,11,3))
c int ,d bit
现在要得出charindex('HEA',a)>0时,a的值一样时,b='HEA' 且d=1的c大于d=0的数目在线等待!!!
a b c d
2006-04-25HEABUL HEA 115 1
2006-04-25HEABUL BUL 108 0
这两条a中存在HEA,比较c的大小
A B C D
...... HEA 100 1
...... HEA 200 0
...... HEA 300 1
...... HEA 400 0你认为是怎么比?如果
上驷对上驷,下驷对下驷
那么100<200,300<400,D=1比D=0大的个数为0如果
上驷对下驷,下驷对上驷
那么100<400,300>200,D=1比D=0大的个数为1你说到底是什么??
select * from tablename a
where b='HEA' and d=1
and exists (
select 1 from tablename
where a=a.a
and d=0
and c<a.c
)
2005-01-01HEABUL HEA 100 1
2005-01-01HEABUL BUL 89 0
2005-01-02HEASUN HEA 120 1
2005-01-02HEASUN SUN 112 0当第一个字段一样时,比较c的大小
即2005-01-01HEABUL时 比较b=hea,d=1的100 和b=bul d=0的89的大小
2006-04-24PISBUC PIS 92 1
2006-04-24PISBUC BUC 74 0
2006-04-24NETPAC NET 88 1
2006-04-24NETPAC PAC 90 0
2006-04-24SUNLAK SUN 107 1
2006-04-24SUNLAK LAK 102 0
2006-04-24MAVGRI MAV 103 1
2006-04-24MAVGRI GRI 93 0
2006-04-25HEABUL HEA 115 1
2006-04-25HEABUL BUL 108 0
2006-04-25CLINUG CLI 98 1
FROM tb x
INNER JOIN tb y
ON x.a=y.a AND x.c>y.c
WHERE x.d=1 AND CHARINDEX('HEA',x.a)>0 AND b='HEA' AND y.d=1??
FROM tb x
INNER JOIN tb y
ON x.a=y.a AND x.c>y.c
WHERE x.d=1 AND CHARINDEX('HEA',x.a)>0 AND x.b='HEA' AND y.d=0
select sum(case when a.c>b.c then 1.0 else 0.0 end)/sum(1) as 热火主场胜率
from tablename a,tablename b
where a.b='HEA' and a.d=1
and a.a=b.a
and b.d=0
结果正确!!!