sql 这样的视图怎么写？

Question

我要根据 table 来做个视图  这个视图里要加一个字段 是table表没有的字段 比如是 count并且这个字段的值 是通过 查询table里其他字段得到的 比如 count(id)的个数 填充到 视图的 count这个字段下怎么写？大概就行

Answer 1

select *,(select count(*) from tb where id=a.id)ct from tb a

Answer 2

create view v_name
as
begin
    select *,count(*)over() as [count] from tb
endselect * from v_name

Answer 3

不是  楼上你看我写的create view View_Toc ("Toc_Idn","Parent_Idn","Title","Link","DescendantCount","Product_Idn")
as
select Toc.Toc_Idn as "Toc_Idn",
       Toc.Parent_Idn as "Parent_Idn",
       Toc.Title as "Title",
       Toc.Link as "Link",
       
       Toc."Product_Idn" as "Product_Idn"
from Toc我点 的地方 就是要填的，需要填的是 从 toc 表 某个字段的count..难道可以？create view View_Toc ("Toc_Idn","Parent_Idn","Title","Link","DescendantCount","Product_Idn")
as
select Toc.Toc_Idn as "Toc_Idn",
       Toc.Parent_Idn as "Parent_Idn",
       Toc.Title as "Title",
       Toc.Link as "Link",
       (SELECT COUNT(ZIDUAN) FROM TOC) AS DescendantCount,
       Toc."Product_Idn" as "Product_Idn"
from Toc不行吧

Answer 4

create view View_Toc ("Toc_Idn","Parent_Idn","Title","Link","DescendantCount","Product_Idn")
as
select Toc.Toc_Idn as "Toc_Idn",
  Toc.Parent_Idn as "Parent_Idn",
  Toc.Title as "Title",
  Toc.Link as "Link",
  COUNT(ZIDUAN) over() AS DescendantCount,
  Toc."Product_Idn" as "Product_Idn"
from Toc

Answer 5

不过,貌似你不是MSSQL吧,怎么弄那么多双引号!

Answer 6

create view View_Toc 
as
select Toc.Toc_Idn as "Toc_Idn",
  Toc.Parent_Idn as "Parent_Idn",
  Toc.Title as "Title",
  Toc.Link as "Link",
  (SELECT COUNT(ZIDUAN) FROM TOC) AS DescendantCount,
  Toc."Product_Idn" as "Product_Idn"
from Toc
go

Answer 7

谢谢两位，但是我的问题没这么简单
我给表结构吧表的结构 ID paerntId ....
现在视图的结构要是 ID paerntId DescendantCount ...
加粗的是多出来的并且他的值是 select count(paerntId) from table where paerntId = ID就是我要判断这个ID 下 有多少条记录 是 paerntId = ID 的 这个值 对应DescendantCount

Answer 8

没关系 我的是oracle 的写法 sqlserver 也支持的

Answer 9

create view View_Toc 
as
select Toc.Toc_Idn as "Toc_Idn",
  Toc.Parent_Idn as "Parent_Idn",
  Toc.Title as "Title",
  Toc.Link as "Link",
  (SELECT COUNT(ZIDUAN) FROM TOC where paerntId=a.paerntId) AS DescendantCount,
  Toc."Product_Idn" as "Product_Idn"
from Toc a
go

Answer 10

create view View_Toc ("Toc_Idn","Parent_Idn","Title","Link","DescendantCount","Product_Idn")
as
select Toc.Toc_Idn as "Toc_Idn",
       Toc.Parent_Idn as "Parent_Idn",
       Toc.Title as "Title",
       Toc.Link as "Link",
       (select COUNT(Parent_Idn) from Toc where Toc_Idn = 2) as "DescendantCount",
       Toc."Product_Idn" as "Product_Idn"
from Toc像我写的 但是上面的2 是ID 应该是个变量  感觉不行

Answer 11

晕菜,1楼不是给你方法了么.select 
a,
b,
c,
(select count(*) from tb where id=a.id)d,
e,
f
from tb a
在表的最后添加了一个别名 a,新增列统计与这条记录相同的id.

Answer 12

我就这样写的
create view View_Toc ("Toc_Idn","Parent_Idn","Title","Link","DescendantCount","Product_Idn")
as
select a.Toc_Idn as "Toc_Idn",
       a.Parent_Idn as "Parent_Idn",
       a.Title as "Title",
       a.Link as "Link",
       (select COUNT(*) from Toc where Toc_Idn = a.Parent_Idn) as "DescendantCount",
       a."Product_Idn" as "Product_Idn"
from Toc a 

Answer 13

你看结果select * from View_Toc
select * from toc2 0 Configuration /Content/Windows/LPs/zz_lp_toolbox_configuration.htm 0 22 0 Configuration /Content/Windows/LPs/zz_lp_toolbox_configuration.htm 27 2表里是27  视图里是0

Answer 14

select a.Toc_Idn as "Toc_Idn",
  a.Parent_Idn as "Parent_Idn",
  a.Title as "Title",
  a.Link as "Link",
  b.CC as "DescendantCount",
  a."Product_Idn" as "Product_Idn"
from Toc a 
LEFT JOIN 
(SELECT Toc_Idn, COUNT(Toc_Idn) AS "CC" FROM Toc GROUP BY Toc_Idn) b
ON a.Parent_Idn=b.CC