两个表,一个辩论表,一个辩论评论表。
结构如下:
CREATE
TABLE
[
dbo
]
.
[
FA_Debate
]
(
[
fdDebaID
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NOT
NULL
,
[
fdDebaTitle
]
[
nvarchar
]
(
500
) COLLATE Chinese_PRC_CI_AS
NOT
NULL
,
[
fdDebaContent
]
[
ntext
]
COLLATE Chinese_PRC_CI_AS
NOT
NULL
,
[
fdDebaCreateAt
]
[
datetime
]
NOT
NULL
DEFAULT
(
getdate
()),
[
fdDebaActorCount
]
[
int
]
NOT
NULL
DEFAULT
((
0
)),
[
fdDebaSquareView
]
[
ntext
]
COLLATE Chinese_PRC_CI_AS
NULL
,
[
fdDebaSquareCount
]
[
int
]
NOT
NULL
DEFAULT
((
0
)),
[
fdDebaReverseView
]
[
ntext
]
COLLATE Chinese_PRC_CI_AS
NULL
,
[
fdDebaReverseCount
]
[
int
]
NOT
NULL
DEFAULT
((
0
)),
[
fdDebaNeutralCount
]
[
int
]
NOT
NULL
DEFAULT
((
0
)),
[
fdDebaValidate
]
[
int
]
NOT
NULL
DEFAULT
((
1
)),
[
fdDebaStatus
]
[
int
]
NOT
NULL
DEFAULT
((
1
)),
CONSTRAINT
[
PK_FA_DEBATE
]
PRIMARY
KEY
CLUSTERED
(
[
fdDebaID
]
ASC
)
WITH
(IGNORE_DUP_KEY
=
OFF
)
ON
[
PRIMARY
]
)
ON
[
PRIMARY
]
TEXTIMAGE_ON
[
PRIMARY
]
CREATE
TABLE
[
dbo
]
.
[
FA_Debate_Review
]
(
[
fdDereID
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NOT
NULL
,
[
fdDereDebaID
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
fdDereContent
]
[
ntext
]
COLLATE Chinese_PRC_CI_AS
NOT
NULL
,
[
fdDereUserName
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NOT
NULL
,
[
fdDereUserAcc
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NULL
,
[
fdDereCreateAt
]
[
datetime
]
NOT
NULL
DEFAULT
(
getdate
()),
[
fdDereCreateIP
]
[
nvarchar
]
(
50
) COLLATE Chinese_PRC_CI_AS
NOT
NULL
,
[
fdDereView
]
[
int
]
NOT
NULL
DEFAULT
((
0
)),
[
fdDereStatus
]
[
int
]
NOT
NULL
DEFAULT
((
1
)),
CONSTRAINT
[
PK_FA_DEBATE_REVIEW
]
PRIMARY
KEY
CLUSTERED
(
[
fdDereID
]
ASC
)
WITH
(IGNORE_DUP_KEY
=
OFF
)
ON
[
PRIMARY
]
)
ON
[
PRIMARY
]
TEXTIMAGE_ON
[
PRIMARY
]
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
EXEC
sys.sp_addextendedproperty
@name
=
N
'
MS_Description
'
,
@value
=
N
'
0中立,1正方,2反方。
'
,
@level0type
=
N
'
SCHEMA
'
,
@level0name
=
N
'
dbo
'
,
@level1type
=
N
'
TABLE
'
,
@level1name
=
N
'
FA_Debate_Review
'
,
@level2type
=
N
'
COLUMN
'
,
@level2name
=
N
'
fdDereView
'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
EXEC
sys.sp_addextendedproperty
@name
=
N
'
MS_Description
'
,
@value
=
N
'
0已删除,1正常。
'
,
@level0type
=
N
'
SCHEMA
'
,
@level0name
=
N
'
dbo
'
,
@level1type
=
N
'
TABLE
'
,
@level1name
=
N
'
FA_Debate_Review
'
,
@level2type
=
N
'
COLUMN
'
,
@level2name
=
N
'
fdDereStatus
'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
GO
USE
[
fsblog
]
GO
ALTER
TABLE
[
dbo
]
.
[
FA_Debate_Review
]
WITH
CHECK
ADD
CONSTRAINT
[
FK_FA_Debate_Review_REFERENCE_FA_Debate
]
FOREIGN
KEY
(
[
fdDereDebaID
]
)
REFERENCES
[
dbo
]
.
[
FA_Debate
]
(
[
fdDebaID
]
)
ON
UPDATE
CASCADE
ON
DELETE
CASCADE
当删除辩论评论的时候辩论表的冗于字段fdDebaActorCount(参与人数)、fdDebaSquareCount(正方支持人数)
、fdDebaReverseCount(反方支持人数)字段要相应的减一,SQL语句如下:
UPDATE
D
SET
D.fdDebaActorCount
=
D.fdDebaActorCount
-
1
,D.fdDebaNeutralCount
=
(
CASE
DR.fdDereView
WHEN
0
THEN
D.fdDebaNeutralCount
-
1
ELSE
D.fdDebaNeutralCount
END
)
,D.fdDebaSquareCount
=
(
CASE
DR.fdDereView
WHEN
1
THEN
D.fdDebaSquareCount
-
1
ELSE
D.fdDebaSquareCount
END
)
,D.fdDebaReverseCount
=
(
CASE
DR.fdDereView
WHEN
2
THEN
D.fdDebaReverseCount
-
1
ELSE
D.fdDebaReverseCount
END
)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
FROM
FA_Debate_Review DR
INNER
JOIN
FA_Debate D
ON
D.fdDebaID
=
DR.fdDereDebaID
WHERE
DR.fdDereID
in
(
'
633810305009687500
'
,
'
633812868719988329
'
)
但是为什么是使用以上SQL语句联合更新辩论表那几个字段的时候影响的记录条数只有一条呢,明明指定了2条记录但是联合更新的那些字段只减一呢?不过'633810305009687500','633812868719988329'这2个辩论评论编号都是同一条辩论的,这个应该没有关系吧,它和FA_Debate表联合起来还都是2条记录啊,但是为什么UPDATE的时候就只更新了一条记录呢?