数据分类

2015-10-12 02:37:42 231 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

from sklearn.datasets import make_classification创建分类数据集 make_classification创建用于分类的数据集，官方文档例子： ### 创建模型 def create_model(): # 生成数据 from sklearn.datasets import make_classification X, y = make_classification(n_samples=10000... 阅读详情

表T数据

想得到的结果：

求大神给SQL

...全文

classification dataset 分类数据集 import numpy as np X = np.array([[-1, -1], [-2, -1], [-3, -2], [1, 1], [2, 1], [3, 2]]) Y = np.array([1, 1, 1, 2, 2, 2]) from sklearn.naive_bayes import GaussianNB clf = GaussianNB() #拟合数据 clf.fit(X, ... 阅读详情

dzxccsu

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

道玄希言 2015-10-12

打赏
举报

^_^

搞定了就好了，呵呵

dzxccsu 2015-10-12

打赏
举报

想到一起了，yangb0803
我的方法
;with tbl(id, value) as
(
select '101', 'A01' union all
select '101', 'B01' union all
select '102', 'A01' union all
select '102', 'B01' union all
select '103', 'A01' union all
select '103', 'A02' union all
select '104', 'A01' union all
select '104', 'B01' union all
select '104', 'B02' union all
select '105', 'A01' union all
select '105', 'B01' union all
select '106', 'A01' union all
select '106', 'A02'
)
,
tbl2
as
(
select id, [value] = stuff((select '|' + [value] from tbl t where id = tbl.id for xml path('')) , 1 , 1 , '')
from tbl
group by id

)

select id,e.value,idnum from
(
select id,value=convert(xml,'<root><v>'+replace(b.value,'|','</v><v>')+'</v></root>'),idnum from (
select [value],ROW_NUMBER()Over(ORDER BY [value] ) as idnum
from (
select distinct [value] from tbl2)a
)c
inner join tbl2 b on c.value=b.value)d
outer apply
(select value=C.v.value('.','nvarchar(100)') from d.value.nodes('/root/v')C(v))e

道玄希言 2015-10-12

打赏
举报

;with tbl([编码], [属性]) as
(
select '101', 'A01' union all
select '101', 'B01' union all
select '102', 'A01' union all
select '102', 'B01' union all
select '103', 'A01' union all
select '103', 'A02' union all
select '104', 'A01' union all
select '104', 'B01' union all
select '104', 'B02' union all
select '105', 'A01' union all
select '105', 'B01' union all
select '106', 'A01' union all
select '106', 'A02'
),
tbl2 as
(
select [编码], STUFF((SELECT N','+QUOTENAME(tbl.[属性])
from tbl
where [编码]=T.[编码] for xml path('')
),1,1,N'') as [属性]
from (select distinct [编码] from tbl) T
),
tbl3 as
(
select ROW_NUMBER() OVER(order by MIN([编码])) as noid,
[属性]
from tbl2 group by [属性]
)
select
t1.[编码], t1.[属性], ltrim(STR(3000 + t3.noid)) as [标识]
from tbl as t1
left join tbl2 as t2
on t1.[编码] = t2.[编码]
left join tbl3 as t3
on t2.[属性] = t3.[属性]
order by t3.noid, t1.[编码]

------------------------------------------------------------------------
编码属性标识
101 A01 3001
101 B01 3001
102 A01 3001
102 B01 3001
105 A01 3001
105 B01 3001
103 A01 3002
103 A02 3002
106 A01 3002
106 A02 3002
104 A01 3003
104 B01 3003
104 B02 3003

dzxccsu 2015-10-12

打赏
举报

感谢@yangb0803的回复
我的实际属性值是很多的，而且相同的数量也是不定的，
想得到的结果：
编码属性标识
101 A01 3001
101 B01 3001
102 A01 3001
102 B01 3001
105 A01 3001
105 B01 3001
103 A01 3002
103 A02 3002
106 A01 3002
106 A02 3002
104 A01 3003
104 B01 3003
104 B02 3003

道玄希言 2015-10-12

打赏
举报

;with tbl([编码], [属性]) as
(
select '101', 'A01' union all
select '101', 'B01' union all
select '102', 'A01' union all
select '102', 'B01' union all
select '103', 'A01' union all
select '103', 'A02' union all
select '104', 'A01' union all
select '104', 'B01' union all
select '104', 'B02' union all
select '105', 'A01' union all
select '105', 'B01' union all
select '106', 'A01' union all
select '106', 'A02'
),tbl2 as(
select [编码],
isnull([A01], 0) as [A01],isnull([A02], 0) as [A02],
isnull([B01], 0) as [B01],isnull([B02], 0) as [B02]
from (select [编码], [属性], 1 as i from tbl) as tbl
pivot
(
max(i) for [属性] in ([A01],[A02],[B01],[B02])
) as T
)

-- 结果1
select tbl.[编码], tbl.[属性] from tbl
inner join tbl2 as t
on tbl.编码 = t.编码
where t.[A01]=1 and t.[B01]=1 and t.[A02]!=1 and t.[B02]!=1

-- 结果2
select tbl.[编码], tbl.[属性] from tbl
inner join tbl2 as t
on tbl.编码 = t.编码
where t.[A01]=1 and t.[A02]=1 and t.[B01]!=1 and t.[B02]!=1

-- 结果3
select tbl.[编码], tbl.[属性] from tbl
inner join tbl2 as t
on tbl.编码 = t.编码
where t.[A01]=1 and t.[B01]=1 and t.[B02]=1 and t.[A02]!=1

dzxccsu 2015-10-12

打赏
举报

这样表达吧
想得到的结果：
编码属性标识
101 A01 3001
101 B01 3001
102 A01 3001
102 B01 3001
105 A01 3001
105 B01 3001
103 A01 3002
103 A02 3002
106 A01 3002
106 A02 3002
104 A01 3003
104 B01 3003
104 B02 3003

qq_17482963 2015-10-12

打赏
举报

看不懂...
不是3个select语句吗

dzxccsu 2015-10-12

打赏
举报

没人么？

数据分类

相关推荐