~~~~~~~~~一句Sql,马上给分？~~~~~~~

[hxm20003]

Tabel A：
name b_type qty
外套画报 B 10
外套画报 C 5

Tabel B:
id b_type
1 A
2 B
3 C
4 D

需要结果：
name b_type qty percent
外套画报 A 0 0%
外套画报 B 10 67%
外套画报 C 5 33%
外套画报 D 0 0%

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-09-10 09:49:43
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[TabelA]
if object_id('[TabelA]') is not null drop table [TabelA]
go 
create table [TabelA]([name] varchar(8),[b_type] varchar(1),[qty] int)
insert [TabelA]
select '外套画报','B',10 union all
select '外套画报','C',5
--> 测试数据：[TabelB]
if object_id('[TabelB]') is not null drop table [TabelB]
go 
create table [TabelB]([id] int,[b_type] varchar(1))
insert [TabelB]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'
--------------开始查询--------------------------


select '外套画报',b.b_type,b.*,SUBSTRING(CONVERT(VARCHAR(20),(CASE WHEN a.qty IS NULL THEN 0 ELSE qty END )*1.0/(SELECT SUM(qty)qty FROM [TabelA] )*100),1,4)+'%'
from [TabelA] a FULL JOIN [TabelB] b ON a.b_type=b.b_type
ORDER BY id
----------------结果----------------------------
/* 
         b_type id          b_type 
-------- ------ ----------- ------ ---------
外套画报     A      1           A      0.00%
外套画报     B      2           B      66.6%
外套画报     C      3           C      33.3%
外套画报     D      4           D      0.00%
*/

[Andy__Huang]

select b.*,cast((cast(a.qty as decimal)/(select SUM(qty) from a))*100 as varchar)+'%'
from b
left join a on b.b_type=a.b_type

[--小F--]

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2013-09-10 09:57:04
-- Verstion:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) 
--	Apr  2 2010 15:53:02 
--	Copyright (c) Microsoft Corporation
--	Data Center Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)
--
----------------------------------------------------------------
--> 测试数据：[TabelA]
if object_id('[TabelA]') is not null drop table [TabelA]
go 
create table [TabelA]([name] varchar(8),[b_type] varchar(1),[qty] int)
insert [TabelA]
select '外套画报','B',10 union all
select '外套画报','C',5
--> 测试数据：[TabelB]
if object_id('[TabelB]') is not null drop table [TabelB]
go 
create table [TabelB]([id] int,[b_type] varchar(1))
insert [TabelB]
select 1,'A' union all
select 2,'B' union all
select 3,'C' union all
select 4,'D'
--------------开始查询--------------------------
SELECT
	ISNULL(A.NAME,'外套画报') AS NAME,B.[b_type],ISNULL(A.qty,0) AS qty,
	ISNULL(LTRIM(CAST( A.qty*1.0/(SELECT SUM(QTY) FROM TabelA)*100 AS DEC(18,0)))+'%','0%') AS [percent]
FROM
	TabelA A
FULL JOIN
	TabelB B
ON
	A.b_type=B.b_type
ORDER  BY
    B.b_type
----------------结果----------------------------
/* NAME     b_type qty         percent
-------- ------ ----------- ------------------------------------------
外套画报     A      0           0%
外套画报     B      10          67%
外套画报     C      5           33%
外套画报     D      0           0%

(4 行受影响)
*/

[Andy__Huang]

select b.*,isnull(cast(cast(100.00*a.qty/a.total as numeric(12,1)) as varchar),'0.00')+'%' as [percent]
from [TabelB] b
left join 
	(select *
	from [TabelA],(select SUM(qty)as total from [TabelA])t
	)a on b.b_type=a.b_type

/*
id	b_type	percent
1	A	0.00%
2	B	66.7%
3	C	33.3%
4	D	0.00%
*/

[hxm20003]

其实，还是不对，‘外套画报’不能写死啊。