22,209
社区成员
发帖
与我相关
我的任务
分享
/*================================
Author:sql_lover
Date:2010-10-25 10:26:29.953
Function:
=================================*/
/*Create Tables*/
create table hotel_count (name varchar(30),id1 int,id2 int)
insert into hotel_count
select '张三', 1, 1 union all
select '李四', 1, 0 union all
select '张三', 2, 0 union all
select '张三', 3, 0 union all
select '李四', 2, 1 union all
select '李四', 3, 1 union all
select '王五', 3, 1
/*How To*/
create view view_hotel_count as
select *,ROW_NUMBER()over(order by name) as serial_num from hotel_count
create proc pro_find_pbt @id1 int as
begin
declare @n1 int
declare @n2 int
declare @f1 float
select @n1=COUNT(1) from view_hotel_count a,view_hotel_count b where a.id1=b.id1 and b.id1=@id1 and a.serial_num<>b.serial_num
select @n2=COUNT(1) from view_hotel_count a,view_hotel_count b where a.id1=b.id1 and b.id1=@id1 and a.serial_num<>b.serial_num and a.id2<>b.id2
if @n1 >0
select CAST(@n2 as float)/CAST(@n1 as float) as probability
end
/*Test*/
exec pro_find_pbt 3
/*Result*/
/*================================
probability
----------------------
0.666666666666667
(1 行受影响)
=================================*/
--> 测试数据:#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(姓名 varchar(8), 记录A int, 记录B int)
insert into #
select '张三', 1, 1 union all
select '李四', 1, 0 union all
select '张三', 2, 0 union all
select '李四', 2, 1 union all
select '王五', 3, 1
select 记录A, 记录B, count(1)投注 from # group by 记录A, 记录B
/*
记录A 记录B 投注 -- 这里应该是sum(金额)
----------- ----------- -----------
1 0 1
2 0 1
1 1 1
2 1 1
3 1 1
*/